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

This error occur when any command or user running the queries doesn’t get the enough free space in the temporary tablespace to complete the task.

Generally DB automatically cleans it up the unused temporary segments so it’s recommended not to increase this space too high on production environment. If this warning or error coming continuously then below are some queries which can help you to identify the root cause of the issue.

These queries will shoe you all the details related to tablespace usage and the sqladdress on which it’s taking time. There may be some queries which retrieve the list of columns for a given table may be responsible for this issue so as solution, the column information is included in the TableInformation which can be reused instead of executing the query repeatedly.

Query To Check TEMP Tablespace Usage:

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;

TABLESPACE                          MB_TOTAL      MB_USED      MB_FREE
------------------------------- ------------ ------------ ----------------------
TEMP                               196604.953125    9        196595.953125

The following query will display the information about SID, USERNAME, TABLESPACE and the SQL statement taking time.

SELECT   S.sid || ',' || S.serial# sid_serial, S.username,T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
T.sqladdr address, Q.hash_value, Q.sql_text
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 S.sid;

66,12575		1	TEMP_G2	0000000543C4EBE0	2202673918  SQL_QUERIES
66,12575		8	TEMP_G2	0000000543C4EBE0	2202673918  SQL_QUERIES
388,48383	DBSNMP	1	TEMP_G1	00000005375C5300	3082800470  SQL_QUERIES


Run the above query at regular intervals to know which statement is eating up temp space.

To add datafiles to the temp tablespace:

select file_name,tablespace_name,bytes/1024/1024/1024, maxbytes/1024/1024/1204,autoextensible from dba_temp_files;

ALTER TABLESPACE TEMP ADD TEMPFILE 'LOCATION' size 100m reuse autoextend on next 50m maxsize 20G

To avoid the ORA-1652 error, you can configure the temp tablespace usage alerts periodically and monitor the sessions/queries causing the temp issues.