Scops
Docs Download

Oracle problems

Oracle Temp Tablespace Full: ORA-01652, Who Is Using TEMP, and How to See It Coming

2 November 2026

ORA-01652 means a statement asked TEMP for one more extent and there was none. The report fails, the batch stops, and the first question is always the same: who is using all of it?

What the error says

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP. The number is the extent, in blocks, that the statement could not get. The tablespace name matters more than the number:

The session that gets the error is often not the one that filled TEMP. One large hash join can hold most of the tablespace for an hour, and every smaller sort that starts after it fails.

How full TEMP is right now

SELECT tablespace_name,
       ROUND(tablespace_size  / POWER(1024, 3), 1) AS size_gb,
       ROUND(allocated_space  / POWER(1024, 3), 1) AS allocated_gb,
       ROUND(free_space       / POWER(1024, 3), 1) AS free_gb,
       ROUND((tablespace_size - free_space)
             / tablespace_size * 100)              AS pct_used
FROM   dba_temp_free_space;

FREE_SPACE counts the extents that are allocated but free for reuse, so it is the number that matters. Temp extents are not released when a sort finishes: they stay allocated to the tablespace and are reused by the next sort. A TEMP that shows 100% allocated and 60% free is fine. A TEMP with 2% free is minutes from the error.

Who is using it

SELECT s.inst_id, s.sid, s.serial#, s.username, s.module, s.sql_id,
       u.tablespace, u.segtype,
       ROUND(SUM(u.blocks) * t.block_size / POWER(1024, 2)) AS temp_mb
FROM   gv$tempseg_usage u
JOIN   gv$session      s ON s.inst_id = u.inst_id
                        AND s.saddr   = u.session_addr
                        AND s.serial# = u.session_num
JOIN   dba_tablespaces t ON t.tablespace_name = u.tablespace
GROUP  BY s.inst_id, s.sid, s.serial#, s.username, s.module,
          s.sql_id, u.tablespace, u.segtype, t.block_size
ORDER  BY temp_mb DESC;

SEGTYPE says what kind of work holds the space: SORT, HASH, DATA for a global temporary table, LOB_DATA or INDEX. A session with 40 GB of HASH is a join whose build side did not fit in memory. Gigabytes of DATA is a global temporary table that a session filled and never cleared.

SQL_ID is the statement that allocated the space. If its plan changed this week, the join method usually changed with it: a nested loop became a hash join, and the hash join spills. The Insights page of Scops lists the statements whose plan changed, with the old and the new plan.

Why a statement spills

A sort or a hash join works in PGA memory first and goes to TEMP when it needs more than its work area may take. PGA_AGGREGATE_TARGET sets the total for the instance, and one serial work area gets only a share of it, so a larger target does not always keep one very large join in memory. What runs now:

SELECT w.inst_id, w.sid, w.sql_id, w.operation_type, w.policy,
       ROUND(w.work_area_size / POWER(1024, 2)) AS work_area_mb,
       ROUND(w.actual_mem_used / POWER(1024, 2)) AS mem_used_mb,
       ROUND(w.tempseg_size    / POWER(1024, 2)) AS temp_mb,
       w.number_passes
FROM   gv$sql_workarea_active w
ORDER  BY w.tempseg_size DESC NULLS LAST;

NUMBER_PASSES of 1 is a one-pass operation: the data went to TEMP once. A value above 1 is multipass: the work area is far too small for the data, and the statement reads and writes TEMP several times. Those are the statements that fill TEMP and run for hours.

Fix it, in this order

  1. Stop the statement that holds TEMP, if it is a runaway report and not the payroll run. ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id' IMMEDIATE; frees its extents for the next sort at once.
  2. Give TEMP room. Add a tempfile, or let the existing one grow. Both run online: 
    ALTER TABLESPACE temp ADD TEMPFILE '+DATA' SIZE 8G
      AUTOEXTEND ON NEXT 1G MAXSIZE 32G;

ALTER DATABASE TEMPFILE '/u02/oradata/ORCL/temp01.dbf'
      AUTOEXTEND ON NEXT 1G MAXSIZE 32G;
    A smallfile tempfile stops at 4,194,303 blocks, which is 32 GB with an 8 KB block. Add a second file instead of fighting that limit.
  3. Fix the statement. A hash join that spills 40 GB usually joins more rows than it needs: a filter that is missing, an index that is gone, or a plan that changed. The ten largest TEMP users of the week are a better tuning list than the ten slowest statements.
  4. Give the work areas more memory if many statements spill a little. V$PGA_TARGET_ADVICE shows what a larger PGA_AGGREGATE_TARGET would save before you change it.
  5. Separate the batch from the users. A second temporary tablespace for the batch account, with ALTER USER batch TEMPORARY TABLESPACE temp_batch;, lets the nightly load fill its own TEMP without failing every small sort of the application.

ALTER TABLESPACE temp SHRINK SPACE KEEP 8G; gives space back to the disk, not to the sessions. It helps after an incident, when the tempfile grew and the disk is now the problem.

See it coming

TEMP rarely fills in a second. It climbs over an hour while one statement runs, or over a week while a plan degrades. Both are visible before the error, if something looks every few minutes. Radar Engine, the detection layer of Scops, watches TEMP with two detectors:

DetectorClockWhen it fires
Temp tablespace nearly fullHourly, over 24 hoursAverage 75%, or three hours at 90%: Watch. Three hours at 90%, rising, under 5 GB free: Warning.
Temp space fillingLive, every 5 minutes95% and rising: Watch. 98%, or sessions already wait for TEMP space: Critical.

A Critical TEMP finding is one of the urgent findings that Radar mails at once, without the usual wait. With the OS credential of the database, the log detectors also report ORA-01652 from the alert log. And because Scops samples the sessions of every database every 5 seconds, the session and the statement that held TEMP at the time of the error are in its history, with or without the Diagnostics Pack.

Questions

Does ORA-01652 always mean TEMP is full?
No. If the tablespace in the message is a permanent tablespace, a CREATE INDEX, CREATE TABLE AS SELECT or ALTER TABLE MOVE ran out of space there. Only a temporary tablespace in the message points at TEMP.

Why does TEMP show 100% used when nothing is running?
Temp extents stay allocated after a sort finishes and are reused by the next one. Read FREE_SPACE in DBA_TEMP_FREE_SPACE, or the blocks in V$TEMPSEG_USAGE, not the allocated size.

Does a larger PGA_AGGREGATE_TARGET stop the spills?
It helps many small spills. One serial work area is limited to a share of the target, so one very large hash join can still spill. Fix the statement, or run it in parallel, which gives each parallel server its own work area.

Can I drop and recreate TEMP to shrink it?
You can create a new temporary tablespace, make it the default, and drop the old one when the sessions that use it finish. ALTER TABLESPACE SHRINK SPACE does the same without the switch.

Related problems

Sources