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

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

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

Usually SMON automatically clean up the unused temporary segments so increasing the temporary tablespace is not a good idea in production environment. If this issue is happening on the regular basis then you need to analyse the temporary tablespace and the sessions along with the queries holding the temp space.

It's better if you can analyse and tune the queries. Below are some of the troubleshooting steps using which you can find the root cause.



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                                  13107122       1293301         17411

The following query will display the information about each statement using space in a sort segment.
This will also including information about  the database sessions that issued the statement and the temporary tablespace and amount of sort space being used.

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;

Run the above query at regular intervals to know which statement is chewing 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 usaage alert and monitor the sessions/queries causing the temp issues.

Read more ...