Oracle ORA-01652 unable to extend temp segment: find the session eating TEMP, fix the query, and size the tablespace properly

#Symptoms

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

• Reports or batch jobs fail at the same step each night.
• CREATE INDEX or ALTER TABLE ... MOVE fails partway through.
• The alert log shows the same error, sometimes for a permanent tablespace instead of TEMP.

#First: which tablespace?

For a permanent tablespace, the fix is free space there (add a datafile or enable autoextend), not TEMP. The rest of this post focuses on TEMP.

#Step 1: How big is TEMP, and how full?

SELECT tablespace_name,
       ROUND(tablespace_size / 1024 / 1024 / 1024, 1) AS size_gb,
       ROUND(allocated_space / 1024 / 1024 / 1024, 1) AS allocated_gb,
       ROUND(free_space      / 1024 / 1024 / 1024, 1) AS free_gb
FROM   dba_temp_free_space;

The columns in dba_temp_free_space are in bytes. free_space counts space inside the tempfiles that can be reused, so TEMP can look "fully allocated" while still having free space.

Tempfiles and autoextend settings:

SELECT file_name, ROUND(bytes/1024/1024/1024,1) AS gb, autoextensible,
       ROUND(maxbytes/1024/1024/1024,1) AS max_gb
FROM   dba_temp_files;

#Step 2: Who is using TEMP right now?

SELECT s.sid, s.serial#, s.username, s.program, s.module, u.sql_id,
       u.segtype,
       ROUND(u.blocks * t.block_size / 1024 / 1024) AS temp_mb
FROM   v$tempseg_usage u
JOIN   v$session       s ON s.saddr = u.session_addr
JOIN   dba_tablespaces t ON t.tablespace_name = u.tablespace
ORDER  BY temp_mb DESC
FETCH FIRST 10 ROWS ONLY;

segtype tells you why: SORT, HASH, LOB_DATA, DATA (global temporary tables), INDEX.

#Step 3: Who used it when it failed (after the fact)

The error usually happens at night. Active Session History keeps TEMP_SPACE_ALLOCATED per sample (Diagnostics Pack licence required):

SELECT sql_id, session_id, MAX(temp_space_allocated)/1024/1024/1024 AS max_temp_gb,
       MIN(sample_time) AS first_seen, MAX(sample_time) AS last_seen
FROM   dba_hist_active_sess_history
WHERE  sample_time BETWEEN TIMESTAMP '2026-11-01 01:00:00' AND TIMESTAMP '2026-11-01 04:00:00'
  AND  temp_space_allocated > 0
GROUP  BY sql_id, session_id
ORDER  BY max_temp_gb DESC
FETCH FIRST 10 ROWS ONLY;

Get the text:

SELECT sql_id, sql_text FROM dba_hist_sqltext WHERE sql_id = '&sql_id';

Tip

Without the Diagnostics Pack, set up a small scheduler job that snapshots v$tempseg_usage every minute into a table during the batch window. Cheap, and it answers "who" the next morning.

#Step 4: Add space (to get the job through)

Add a tempfile, or let the existing one grow:

ALTER TABLESPACE temp ADD TEMPFILE '+DATA' SIZE 32G AUTOEXTEND ON NEXT 1G MAXSIZE 64G;
-- or, on file systems
ALTER TABLESPACE temp ADD TEMPFILE '/u02/oradata/PROD/temp02.dbf' SIZE 32G AUTOEXTEND ON NEXT 1G MAXSIZE 64G;

ALTER DATABASE TEMPFILE '/u02/oradata/PROD/temp01.dbf' AUTOEXTEND ON NEXT 1G MAXSIZE 64G;

Check the disk group or file system has the room first. Tempfiles are sparse on some file systems, so free space can disappear later when the file is actually written.

Warning

Unlimited autoextend on TEMP lets one bad query fill the whole disk group or file system, which can then stop redo/archivelog writes and halt the database. Always set MAXSIZE.

#Step 5: Fix the query that spills

Adding space treats the symptom. Look at the plan of the top SQL_ID:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', NULL, 'ALLSTATS LAST +PEEKED_BINDS'));
-- or from AWR
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('&sql_id'));

Look at Used-Tmp / TempSpc columns and these patterns:

Stale statistics are a frequent cause of sudden temp spikes:

SELECT owner, table_name, last_analyzed, stale_stats
FROM   dba_tab_statistics
WHERE  owner = 'APP' AND (stale_stats = 'YES' OR last_analyzed < SYSDATE - 30);

#Step 6: Memory settings that affect spilling

Sorts and hash joins spill to TEMP when they exceed their work area. With automatic PGA management:

SHOW PARAMETER pga_aggregate_target;
SHOW PARAMETER pga_aggregate_limit;

SELECT name, value FROM v$pgastat
WHERE  name IN ('aggregate PGA target parameter', 'total PGA allocated',
                'over allocation count', 'cache hit percentage');

A non-zero and growing over allocation count, or a low cache hit percentage, suggests PGA is undersized for the workload. Raising pga_aggregate_target can reduce spilling, but check server memory first.

#Step 7: Separate heavy users

Give batch or reporting users their own temporary tablespace (or tablespace group) so a runaway report can't break OLTP:

CREATE TEMPORARY TABLESPACE temp_batch TEMPFILE '+DATA' SIZE 32G AUTOEXTEND ON NEXT 1G MAXSIZE 128G;
ALTER USER batch_user TEMPORARY TABLESPACE temp_batch;

In 12.2+ multitenant, each PDB can have its own TEMP; check you're sizing the PDB's TEMP, not only the CDB's.

#Prevent it next time

• Set MAXSIZE on every tempfile and alert when TEMP usage exceeds 80%.
• Capture TEMP usage per SQL_ID during batch windows (ASH or your own snapshots).
• Keep optimizer statistics fresh, especially after big loads.
• Review the top TEMP-consuming SQL after each release.
• Separate TEMP for batch/reporting users.