000028161 - KB-1287 - ORA-01652 : unable to extend temp segment by 128 in tablespace TEMP

Document created by RSA Customer Support Employee on Jun 14, 2016•Last modified by RSA Customer Support Employee on Apr 21, 2017
Version 2Show Document
  • Comment • 0
  • View in full screen mode

Article Content

Article Number000028161
Applies ToAffected Versions: All Versions
IssueORA-01652 : unable to extend temp segment by 128 in tablespace TEMP
CauseIt's best to contact support if you receive this error it could be a problem with the underlying packages.
Get the java stack trace from the log file and submit it for analysis. If support indicates that you should increase the temp table size the procedure is:
 

How to find the status of TEMP tablespace


  1. Login to the appliance as the oracle
  2. Login to database as user sys : sqlplus "/as sysdba"
  3. Run the below commands:

    SELECT d.tablespace_name, NVL(a.bytes / 1024 / 1024, 0) Total , NVL(t.bytes, 0)/1024/1024 used FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes, count(file_id) count from dba_temp_files group by tablespace_name) a, (select ss.tablespace_name , sum((ss.used_blocks*ts.blocksize)) bytes from gv$sort_segment ss, sys.ts$ ts where ss.tablespace_name = ts.name group by ss.tablespace_name) t WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+) and d.tablespace_name like 'TEMP%';



SELECT sum(free_blocks)
from gv$sort_segment
where tablespace_name = 'TEMP'



 


 


4. If the temp tablespace is full and has reached 32GB then as a workaround you can alter it as shown below.
Login to the server using unix user “oracle". Login to database user “sys” : sqlplus "/as sysdba Run the below command:


    1.  

      ALTER TABLESPACE "TEMP" ADD TEMPFILE '+DG01' SIZE 2G AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED; Commit;


       

      This command may take 2-5 minutes to complete. 


       
ResolutionIf the temp tablespace is full and has reached 32GB then as a workaround you can alter it as shown below.

Login to the server using unix user “oracle". Login to database user “sys” : sqlplus "/as sysdba Run the below command:


    1.  

      ALTER TABLESPACE "TEMP" ADD TEMPFILE '+DG01' SIZE 2G AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED; Commit;


       

      This command may take 2-5 minutes to complete. 


       

Attachments

    Outcomes