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 Go to next message
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 #640502 is a reply to message #640501] Wed, 29 July 2015 13:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Your queries and their execution plan.
Use AWR/Statspack report to help you.

Re: ORA-01652 error [message #640503 is a reply to message #640501] Wed, 29 July 2015 13:19 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
01652, 00000, "unable to extend temp segment by %s in tablespace %s"
// *Cause: Failed to allocate an extent of the required number of blocks for
// a temporary segment in the tablespace indicated.
// *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
// files to the tablespace indicated.
[oracle@localhost ~]$

After session throws error & SQL terminates, the TEMP space is released so when you issue SQL above all appears OK.

Are you sure that SQL is not doing Cartesian Product?
Re: ORA-01652 error [message #640504 is a reply to message #640502] Wed, 29 July 2015 13:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Also, if you want to continue to get help you should feedback, thanks those who spend time to help you and answer their questions.
Review your previous topic.
If you got a solution then post it.

Re: ORA-01652 error [message #641766 is a reply to message #640504] Fri, 21 August 2015 17:06 Go to previous messageGo to next message
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 #641768 is a reply to message #641766] Sat, 22 August 2015 00:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Michel Cadot wrote on Wed, 29 July 2015 20:20

Also, if you want to continue to get help you should feedback, thanks those who spend time to help you and answer their questions.
Review your previous topic.
If you got a solution then post it.


We are waiting on this.
And your queries are unreadable, learn how to format them.

Re: ORA-01652 error [message #641794 is a reply to message #641766] Mon, 24 August 2015 06:32 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #641812 is a reply to message #641808] Mon, 24 August 2015 22:30 Go to previous messageGo to next message
kapilavastu
Messages: 9
Registered: July 2015
Junior Member
More than encountering ORA-01652, our hot backup taken after this situation happens to be huge. we have allocated 90gig for three of the temporory data files. Our hotbackup size after encoutering ORA-01652 happens to be 103 gig.
Re: ORA-01652 error [message #641813 is a reply to message #641812] Mon, 24 August 2015 23:01 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>we have allocated 90gig for three of the temporory data files. Our hotbackup size after encoutering ORA-01652 happens to be 103 gig.

We have met the enemy & they is us.
Why are you backing up TEMP tablespace?

Problem Exists Between Keyboard And Chair.
Oracle DB only does what it is configured to do or directed to do.
Re: ORA-01652 error [message #641825 is a reply to message #641808] Tue, 25 August 2015 06:14 Go to previous message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
kapilavastu wrote on Mon, 24 August 2015 19:20
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
<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.
Previous Topic: IDLE_TIME terminates the JDBC connection
Next Topic: PGA required for optimal and one-pass execution
Goto Forum:
  


Current Time: Thu Mar 28 08:25:18 CDT 2024