Home » RDBMS Server » Server Administration » ORA-01652 error (12.1.0.1.0, linux)
| ORA-01652 error [message #640501] |
Wed, 29 July 2015 13:14  |
 |
kapilavastu
Messages: 9
Registered: July 2015
|
Junior Member |
|
|
Since moving to 12c under linux, we keep receiving this error.
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
I ran below query and it shows free space. can any one point me into what needs to be checked.
SELECT * from DBA_TEMP_FREE_SPACE;
TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
TEMP 103078133760 103078133760 60231254016
|
|
|
|
|
|
|
|
|
|
| Re: ORA-01652 error [message #641766 is a reply to message #640504] |
Fri, 21 August 2015 17:06  |
|
kapilavastu
Messages: 9
Registered: July 2015
|
Junior Member |
|
|
i am sorry for this late reply as I was on leave. The query sent from Java is a simple query that returns 90000+ records. No missing joins in the query. But we tried multiple users(6) doing the same type of work that resulted in TEMP tablespace being filled. I used below query to check. USED% from below output was raising slowly and finally it hit 100%.
SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management
"ExtManag",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)", TO_CHAR(NVL(t.bytes,
0)/1024/1024,'99999,999.999') ||'/'||TO_CHAR(NVL(a.bytes/1024/1024, 0),'99999,999.999') "Used (M)",
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %"
FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_temp_files group by
tablespace_name) a,
(select tablespace_name, sum(bytes_cached) bytes from
v$temp_extent_pool group by tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY';
Could the execution plan of this query changed from 11.2 to 12c?
select * from
(SELECT d.tablespace_name,a.sid,a.serial#,a.program,a.module,a.action,a.username "DB Username",a.osuser,ROUND((b.blocks*d.block_size)/1024/1024,2) "Used MB",c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c,dba_tablespaces d
WHERE a.saddr = b.session_addr AND c.address= a.sql_address AND c.hash_value = a.sql_hash_value AND d.tablespace_name=b.tablespace ORDER BY b.tablespace, b.blocks DESC)
where rownum <=10
above query resulted in 10 rows that is the same offending query by 10 different sessions.
|
|
|
|
|
|
| Re: ORA-01652 error [message #641794 is a reply to message #641766] |
Mon, 24 August 2015 06:32 |
 |
EdStevens
Messages: 1376
Registered: September 2013
|
Senior Member |
|
|
for you own sanity, not to mention that of those from whom you seek assistance, you should learn to format your code for readability.
It took me all of 5 seconds to format yours:
SELECT *
FROM
(SELECT d.tablespace_name,
a.sid,
a.serial#,
a.program,
a.module,
a.action,
a.username "DB Username",
a.osuser,
ROUND((b.blocks*d.block_size)/1024/1024,2) "Used MB",
c.sql_text
FROM v$session a,
v$tempseg_usage b,
v$sqlarea c,
dba_tablespaces d
WHERE a.saddr = b.session_addr
AND c.address = a.sql_address
AND c.hash_value = a.sql_hash_value
AND d.tablespace_name=b.tablespace
ORDER BY b.tablespace,
b.blocks DESC
)
WHERE rownum <=10 |
|
|
|
| Re: ORA-01652 error [message #641808 is a reply to message #641794] |
Mon, 24 August 2015 19:20 |
|
kapilavastu
Messages: 9
Registered: July 2015
|
Junior Member |
|
|
Thanks Ed. I was going to format it and post it.
Here is the other Select query formatted.
select * from
(SELECT d.tablespace_name,a.sid,a.serial#,a.program,a.module,a.action,a.username "DB Username",
a.osuser,ROUND((b.blocks*d.block_size)/1024/1024,2) "Used MB",c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c,dba_tablespaces d
WHERE a.saddr = b.session_addr AND
c.address= a.sql_address AND
c.hash_value = a.sql_hash_value AND
d.tablespace_name=b.tablespace
ORDER BY b.tablespace, b.blocks DESC)
WHERE rownum <=10
|
|
|
|
|
|
|
|
| Re: ORA-01652 error [message #641825 is a reply to message #641808] |
Tue, 25 August 2015 06:14 |
|
EdStevens
Messages: 1376
Registered: September 2013
|
Senior Member |
|
|
kapilavastu wrote on Mon, 24 August 2015 19:20Thanks Ed. I was going to format it and post it.
Here is the other Select query formatted.
select * from
(SELECT d.tablespace_name,a.sid,a.serial#,a.program,a.module,a.action,a.username "DB Username",
a.osuser,ROUND((b.blocks*d.block_size)/1024/1024,2) "Used MB",c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c,dba_tablespaces d
<snip>
Your code would be even easier to read of you'd put every column name in your SELECT clause, and every table name in your FROM clause, on its own line.
|
|
|
|
Goto Forum:
Current Time: Sat Feb 08 09:50:54 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
