LLI Wiki — My Digital Knowledge Base

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

  1. 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;
    
  2. 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;
Mastodon LLIMastodon SocialFosstodon