ORA-1652: unable to extend temp segment by 128 in tablespace TEMP

In this post covers ORA-1652: unable to extend temp segment by 128 in tablespace TEMP error. Generally we are facing  this ora error while running sql query or concurrent program in our Instance.

The ORA-01652 error occurs due to failure in allocating the extent for the temp segment in the tablespace. The temp segment refers to a temporary tablespace used internally by the Oracle database such as joins. It may be occurs by pulling data from records that are quite extensive.

As per my observation regarding ORA-1652,If your existing sql query (Program) getting this error the likely cause of this issue is that temporary tablespace space issue. To resolve this error we have to add tempfile or increase the existing tempfile. Once we will do the same issue might be resolved.

To check temporary tablespace used and free space.

SELECT   A.tablespace_name tablespace, D.mb_total,

SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,

D.mb_total || SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free FROM     v$sort_segment A, (SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total FROM     v$tablespace B, v$tempfile C WHERE    B.ts#= C.ts# GROUP BY B.name, C.block_size) D WHERE    A.tablespace_name = D.name GROUP by A.tablespace_name, D.mb_total;

Basically we can check how much temp segment can be used by viewing the total_blocks and used_blocks.


SQL> select inst_id, tablespace_name, total_blocks, used_blocks, free_blocks from gv$sort_segment;


Similarly we can check temp segment by per sql statement.

SELECT  S.sid || ',' || S.serial# sid_serial, S.username, Q.hash_value, Q.sql_text, T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace FROM    v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS WHERE   T.session_addr = S.saddr
AND     T.sqladdr = Q.address AND     T.tablespace = TBS.tablespace_name ORDER BY mb_used;


To check tempfile.

SQL> select file_name,tablespace_name from dba_temp_files;


Increase the temporary tablespace size.

1. Add tempfile in existing temp tablespace space.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/data01/GSTUAT3/redotempfiles/temp02.dbf' SIZE 2G;



2. Resize the existing tempfile.

SQL>ALTER DATABASE TEMPFILE '/data01/GSTUAT3/redotempfiles/temp01.dbf' RESIZE 5G;

In another scenario, if you are implementing new program in your instance and facing the ORA-01652 error. After increase the tempfile size if still issue remains same it means any issue in your Query. You have to check your SQL Query to identify and fix the issue as well. If any issue in your query , it will not resolve the ORA-1652: unable to extend temp segment by 128 in tablespace TEMP by increase the space.

To check how much temp space using by particular program/module

SELECT   S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.MODULE,SUBSTR(u.sql_text, 1, 50) as sql_text,
P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.TABLESPACE FROM     v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P,v$sqlarea u WHERE    T.session_addr = S.saddr
AND      S.paddr = P.addr AND      T.TABLESPACE = TBS.tablespace_name AND       S.sql_id  = u.sql_id
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.MODULE,u.sql_text, P.program, TBS.block_size, T.TABLESPACE
having   SUM (T.blocks) * TBS.block_size / 1024 / 1024 > 1
ORDER BY mb_used;


Output:- 

SID_SERIAL USERNAME   OSUSER     SPID   MODULE          SQL_TEXT                  PROGRAM                    MB_USED TABLESPACE
---------- ---------- ---------- ------ --------------- ------------------------- ----------------------- ---------- ----------
1952,8877  APPS       applmgr    13120  e:JA:fwk:fnd.cp SELECT * FROM (select req oracle@samarthdb.ace-cr          2 TEMP3
                                        .viewreq.server uest_id,  program, reques anes.com
                                        .DummyAM

1672,2229  APPS       applmgr    2811   fnd.framework.n BEGIN  :1:=FND_GLOBAL.USE oracle@samarthdb.ace-cr          2 TEMP3
                                        avigate.server. R_ID;  :2:=FND_GLOBAL.RES anes.com
                                        OANavigatePortl
                                        :R



Related Post:- 


How to manage temporary tablespace in oracle.