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.