ORA-01652: unable to extend temp segment in tablespace TEMP
Diagnose and fix ORA-01652: check tempfiles, grow the TEMP tablespace and identify the sessions consuming sort space.
The error ORA-01652 means that a temporary segment (sort, join, aggregation) cannot be extended because the TEMP tablespace has no free space left and its tempfiles cannot grow.
Check the block size and datafile limits
The database block size determines the maximum size of a datafile:
select value from v$parameter where name = 'db_block_size';
| Block Sz | Max Datafile Sz (GB) | Max DB Sz (TB) |
|---|---|---|
| 2 048 | 8 192 | 524 264 |
| 4 096 | 16 384 | 1 048 528 |
| 8 192 | 32 768 | 2 097 056 |
| 16 384 | 65 536 | 4 194 112 |
| 32 768 | 131 072 | 8 388 224 |
List the datafiles and their maximum size:
select bytes/1024/1024 as mb_size,
maxbytes/1024/1024 as maxsize_set,
x.*
from dba_data_files x
Enable unlimited autoextend on an existing file, or add a datafile:
alter database datafile 'path_to_your_file\that_file.DBF' autoextend on maxsize unlimited;
alter tablespace system add datafile 'path_to_your_datafiles_folder\name_of_df_you_want.dbf' size 10m autoextend on maxsize unlimited;
Diagnosis
-
Identify the tempfiles and their tablespace:
select file_name, TABLESPACE_NAME from DBA_TEMP_FILES;Then check the space used in the sort segments:
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; -
Check whether any free space remains in the temporary tablespace.
Temporary fixes
Resize the existing tempfile:
ALTER DATABASE TEMPFILE '/u01/oradata/ORCL/temp01.dbf' RESIZE 3072M;
Or add a tempfile to the TEMP tablespace:
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/oradata/ORCL/temp02.dbf' SIZE 1024M REUSE AUTOEXTEND ON NEXT 50M MAXSIZE 1024M;
Root cause analysis
Identify the sessions using the temporary tablespace:
SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) statements
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
P.program, TBS.block_size, T.tablespace
ORDER BY mb_used;
Find the SQL statement responsible for the sort usage:
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;