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

Introduction: – 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.

Table of Contents

How to resolve ORA-1652 unable to extend temp segment.

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 occurred by pulling data from records that are quite extensive.

As per my observation regarding ORA-1652, If you’re 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;

How to check tempfile.

SQL> select file_name,tablespace_name from dba_temp_files;

Steps to 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 [email protected] 2 TEMP3
.viewreq.server uest_id, program, reques anes.com
.DummyAM

1672,2229 APPS applmgr 2811 fnd.framework.n BEGIN :1:=FND_GLOBAL.USE [email protected] 2 TEMP3
avigate.server. R_ID; :2:=FND_GLOBAL.RES anes.com
OANavigatePortl
:R