ORA-01652 fires when Oracle cannot allocate another extent for a temporary segment in a tablespace. In production the tablespace is almost always the default TEMP, and the consumers are operations that have spilled out of the PGA: large sorts, hash joins, global temporary tables, and temporary LOBs. The error is a hard stop for the failing statement, while every other session that needs temp behind it queues on direct path write temp.

The mechanics matter for triage. Sort and hash work areas live in PGA first. When a work area exceeds what PGA can grant, the operation spills to temp and writes extents there. Those extents are not freed when the sort finishes. They are marked free inside the sort segment and reused by the next operation, which is why DBA_TEMP_FREE_SPACE can look tight even when nothing is actively sorting. The space is only returned when the sort segment itself shrinks, and for global temporary tables or temp LOBs that may not happen until the session disconnects.

Because no redo is generated for temporary segments, ORA-01652 does not endanger recovery or the archive path. It is a throughput and availability problem, not a data integrity problem. The usual root cause is one of two things: a single runaway SQL doing an accidental Cartesian join or a hash join against an unindexed dimension, or a steady-state workload whose temp allocation has quietly grown past what the temp files and PGA can absorb.

See the Oracle Database hub for the broader memory and I/O model that temp fits into.

What this means

The error tells you three things in one line:

  • A session tried to allocate a temp extent.
  • The target tablespace had no free extent large enough to satisfy the request.
  • The statement failed; the session received ORA-01652.

The target tablespace is usually TEMP, but the error text is not limited to it. The short form is unable to extend temp segment by <N> in tablespace <name>. On older releases you must correlate the timestamp with V$TEMPSEG_USAGE or the alert log to find the offender.

A key nuance: a near-full temp tablespace is normal between sort bursts because freed sort extents stay inside the sort segment. The error only fires when the sort segment cannot grow because the underlying temp files cannot extend. So the first question is never “why is temp 95% full” but “which session needed an extent just now that we could not satisfy.”

Common causes

CauseWhat it looks likeFirst thing to check
Runaway sort or hash joinOne SQL_ID dominates V$TEMPSEG_USAGE; direct path write temp spikes for one sessionV$TEMPSEG_USAGE grouped by SQL_ID
Missing join predicate (Cartesian)Same SQL_ID as above, plan shows MERGE JOIN CARTESIAN or hash join with huge build sideV$SQL_PLAN for the SQL_ID
PGA undersized for workloaddirect path read/write temp is the dominant wait class across many sessions, not just oneV$PGASTAT and over allocation count
Temp tablespace genuinely too smallDBA_TEMP_FREE_SPACE.FREE_SPACE near zero at peak, error fires under loadDBA_TEMP_FREE_SPACE trend
Tempfile at MAXSIZEAdding a tempfile did not help; BYTES equals MAXBYTES in DBA_TEMP_FILESDBA_TEMP_FILES autoextend columns
LOCAL_TEMP_TABLESPACE misconfig (12.2+)Sorts landing in SYSTEM instead of TEMP; DBA_USERS.LOCAL_TEMP_TABLESPACE = 'SYSTEM'DBA_USERS for affected schemas
Global temp tables or temp LOBs not releasingMany sessions each holding modest GTT or LOB extents; temp grows over hoursV$TEMPSEG_USAGE filtered by SEGTYPE

Quick checks

Run these read-only. None of them take locks or change state.

-- Current temp tablespace free space (11g+)
SELECT TABLESPACE_NAME,
       TABLESPACE_SIZE/1048576  AS total_mb,
       ALLOCATED_SPACE/1048576  AS allocated_mb,
       FREE_SPACE/1048576       AS free_mb
FROM DBA_TEMP_FREE_SPACE
ORDER BY FREE_SPACE;

-- Top temp consumers by session right now
-- FETCH FIRST requires 12c+; use ROWNUM <= 20 on 11g
SELECT s.SID, s.SERIAL#, s.USERNAME, s.SQL_ID,
       t.SEGTYPE,
       SUM(t.BLOCKS * (SELECT VALUE FROM V$PARAMETER WHERE NAME='db_block_size'))/1048576 AS temp_mb
FROM V$TEMPSEG_USAGE t
JOIN V$SESSION s ON t.SESSION_ADDR = s.SADDR
GROUP BY s.SID, s.SERIAL#, s.USERNAME, s.SQL_ID, t.SEGTYPE
ORDER BY temp_mb DESC
FETCH FIRST 20 ROWS ONLY;

-- Tempfile capacity and autoextend headroom
SELECT TABLESPACE_NAME, FILE_NAME,
       BYTES/1048576     AS current_mb,
       MAXBYTES/1048576  AS max_mb,
       AUTOEXTENSIBLE
FROM DBA_TEMP_FILES
ORDER BY TABLESPACE_NAME, FILE_NAME;

-- PGA pressure indicators
SELECT NAME, VALUE
FROM V$PGASTAT
WHERE NAME IN ('aggregate PGA target parameter',
               'aggregate PGA auto target',
               'total PGA inuse',
               'total PGA allocated',
               'over allocation count',
               'cache hit percentage');

-- Spill waits (PGA to temp) since instance startup
SELECT EVENT, TOTAL_WAITS, TIME_WAITED_MICRO,
       ROUND(TIME_WAITED_MICRO/NULLIF(TOTAL_WAITS,0)/1000, 2) AS avg_ms
FROM V$SYSTEM_EVENT
WHERE EVENT IN ('direct path read temp', 'direct path write temp');

-- Per-user temp tablespace assignment (12.2+)
SELECT USERNAME, TEMPORARY_TABLESPACE, LOCAL_TEMP_TABLESPACE
FROM DBA_USERS
WHERE LOCAL_TEMP_TABLESPACE = 'SYSTEM'
   OR TEMPORARY_TABLESPACE IS NULL;

# Check the alert log for ORA-01652 occurrences
adrci exec="show alert -tail 200" | grep -A2 "ORA-01652"

How to diagnose it

flowchart TD
  A[ORA-01652 reported] --> B{Free space in DBA_TEMP_FREE_SPACE?}
  B -- Near zero --> C[Find top consumer in V$TEMPSEG_USAGE]
  B -- Plenty free --> D[Check tempfile MAXSIZE / autoextend]
  D --> E{BYTES = MAXSIZE and AUTOEXTENSIBLE = YES?}
  E -- Yes --> F[Tempfile hit MAXSIZE ceiling]
  E -- No --> G[Check LOCAL_TEMP_TABLESPACE for SYSTEM misroute]
  C --> H{One SQL_ID dominates?}
  H -- Yes --> I[Inspect plan: Cartesian, hash build, full scan]
  H -- No --> J[Check V$PGASTAT: over-alloc, cache hit percent]
  I --> K[Tune or baseline the SQL]
  J --> L[Resize PGA_AGGREGATE_TARGET or temp]

Work through the flow in this order.

  1. Confirm temp is actually constrained. Start with DBA_TEMP_FREE_SPACE. If FREE_SPACE is healthy and you still got ORA-01652, the constraint is on the tempfile side (MAXSIZE, autoextend off, or underlying filesystem/ASM full), not on logical temp usage.
  2. Find the offender while the error is fresh. V$TEMPSEG_USAGE is sampled live, so the consumer may already be gone by the time you look. If you arrive late, pull the SQL_ID from the error message if the release includes it, or grep the alert log for the ORA-01652 timestamp and correlate with V$ACTIVE_SESSION_HISTORY (Diagnostics Pack required) for the same window.
  3. Distinguish one bad SQL from systemic pressure. If a single SQL_ID owns most of V$TEMPSEG_USAGE, you have a runaway query. If usage is spread across many sessions with direct path write temp as the dominant wait, the system is undersized: PGA is too small relative to concurrent analytics, or temp is too small for the steady-state sort footprint.
  4. Inspect the plan for the runaway SQL_ID. Look for MERGE JOIN CARTESIAN, a hash join whose build side is far larger than the optimizer estimated, a missing join predicate, or a full table scan replacing an index scan. The latter is the same plan regression pattern that drives buffer cache spikes.
  5. Check PGA, not just temp. A temp exhaustion incident is very often a PGA sizing incident in disguise. V$PGASTAT over allocation count growing and cache hit percentage below roughly 80% both mean the instance is spilling far more than it should.
  6. Verify the temp tablespace assignment on 12.2 and later. LOCAL_TEMP_TABLESPACE was introduced in 12.2 and, due to a known upgrade issue, could be set to SYSTEM for some users after upgrading from 12.1. Sorts and hash joins for those users then spill into SYSTEM, which is a far worse failure than ORA-01652 in TEMP.

Metrics and signals to monitor

SignalWhy it mattersWarning sign
DBA_TEMP_FREE_SPACE.FREE_SPACEDirect capacity signal for tempTrending to zero at peak, or below 10%
V$TEMPSEG_USAGE grouped by SQL_IDIdentifies the runaway consumerOne SQL_ID owns a large share of allocated temp
direct path read temp, direct path write temp waitsConfirms spill from PGA to tempBecoming the dominant non-idle wait class
V$PGASTAT total PGA allocated vs PGA_AGGREGATE_TARGETUndersized PGA drives spillAllocated persistently exceeds target
V$PGASTAT over allocation countOracle is exceeding the PGA target repeatedlyCounter growing week over week
V$PGASTAT cache hit percentageRatio of work done in memory vs spilledBelow 80% on an analytics workload
DBA_TEMP_FILES BYTES vs MAXBYTESAutoextend headroom on each tempfileBYTES equals MAXBYTES with AUTOEXTENSIBLE = YES
DBA_USERS.LOCAL_TEMP_TABLESPACECatches the 12.2+ SYSTEM misrouteAny production user set to SYSTEM

Fixes

Free temp capacity immediately

The fastest relief is to add a tempfile or resize an existing one. Both are online operations on temp.

-- Add a tempfile (online, non-disruptive)
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/oradata/TEMP02.dbf' SIZE 4G AUTOEXTEND ON NEXT 512M MAXSIZE 32G;

-- Resize an existing tempfile up to its MAXSIZE
ALTER DATABASE TEMPFILE '/u01/oradata/TEMP01.dbf' RESIZE 16G;

-- Allow a capped tempfile to autoextend further
ALTER DATABASE TEMPFILE '/u01/oradata/TEMP01.dbf' AUTOEXTEND ON MAXSIZE 32G;

Two cautions. First, adding a tempfile does nothing if the new file’s BYTES already equals its MAXSIZE and the underlying filesystem cannot grow. Verify with DBA_TEMP_FILES afterward. Second, the TEMP tablespace itself cannot be taken offline; you can only take individual tempfiles offline, and doing so to the only tempfile in a busy instance is disruptive.

Identify and stop the runaway consumer

If one session is consuming gigabytes of temp, the most direct fix is to kill that session so its sort segment is freed. This is disruptive to that session’s user, so confirm the SQL_ID and owner first.

-- Disruptive: kills the named session. Confirm SID and SERIAL# first.
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

Killing the session frees the sort segment but does not fix the SQL. The same query will fail again the next time it runs. Treat the kill as triage, not remediation.

Fix the SQL

If the plan shows a Cartesian join, a hash join with a massively underestimated build side, or a full scan replacing an index access, the durable fix is in the SQL or its statistics, not in temp sizing.

  • Missing join predicate. Add the join condition. This is the single most common cause of accidental Cartesian blowups.
  • Missing index on the hash build side. A hash join builds the hash table from the smaller input. If the optimizer has the smaller input wrong because of stale stats, the build side explodes. Gather stats, or force a join order hint.
  • Plan regression. If the SQL used to do an index access and now full-scans, load the known-good plan as a SQL Plan Baseline with DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE.
  • Genuinely large analytical query. Some queries are meant to spill. In that case the fix is capacity (PGA and temp), not SQL.

Size PGA correctly

For analytics and mixed workloads, PGA sizing is often the real problem. PGA_AGGREGATE_TARGET is a soft target Oracle tries to honor; PGA_AGGREGATE_LIMIT (12c and later) is the hard cap that raises ORA-04036 when hit. Aim for peak total PGA allocated to stay below roughly 80% of PGA_AGGREGATE_LIMIT, and watch over allocation count.

-- System-wide, affects all sessions immediately
ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 16G SCOPE=BOTH;
ALTER SYSTEM SET PGA_AGGREGATE_LIMIT  = 24G SCOPE=BOTH;

On Linux, remember that SGA (hopefully hugepages) plus PGA plus OS needs must fit in physical RAM. Raising PGA without checking total memory is how you meet the OOM killer.

Correct LOCAL_TEMP_TABLESPACE misrouting

For any user whose LOCAL_TEMP_TABLESPACE is set to SYSTEM, reset it to a real temp tablespace. This affects 12.2 and later instances, especially those upgraded from 12.1.

ALTER USER <username> LOCAL TEMPORARY TABLESPACE TEMP;

Shrink temp to reclaim space (use sparingly)

If temp has grown large due to a one-off event and you want the space back, you can shrink. This is not a fix for ORA-01652 and can fail if active sorts are using the space.

-- 11g+ online shrink, limited by active sort extents
ALTER TABLESPACE TEMP SHRINK SPACE KEEP 8G;

Prevention

  • Trend temp usage over time. DBA_TEMP_FREE_SPACE sampled every few minutes will show whether temp is creeping up week over week. Plan capacity before the cliff.
  • Track the high-water mark. If temp regularly approaches its limit at peak, resize proactively rather than reacting to the next ORA-01652.
  • Watch PGA pressure signals. V$PGASTAT over allocation count and cache hit percentage are leading indicators that temp is about to become the bottleneck.
  • Use SQL Plan Baselines for critical analytical SQL. Plan regressions are the most common single cause of sudden temp exhaustion from a query that used to fit in PGA.
  • Review LOCAL_TEMP_TABLESPACE after every upgrade. Especially upgrades that pass through 12.2.
  • Validate autoextend headroom on tempfiles. A tempfile with AUTOEXTENSIBLE = YES and BYTES = MAXBYTES is a capped tempfile in disguise.

How Netdata helps

Oracle Database monitoring with Netdata surfaces the signals that catch ORA-01652 before it becomes a page:

  • Per-second temp tablespace utilization from DBA_TEMP_FREE_SPACE, so you see the climb toward the cliff rather than only the moment of failure.
  • direct path read temp and direct path write temp wait time tracked alongside the other wait classes, so spill from PGA to temp is visible as a rising share of DB time.
  • PGA aggregate metrics from V$PGASTAT correlated with temp usage, the single most useful correlation for distinguishing “one bad query” from “systemic undersizing.”
  • Anomaly detection on temp usage and PGA allocation, which catches the slow drift that static thresholds miss.
  • Alert log ingestion that surfaces ORA-01652, ORA-04036, and related space errors as they happen, with the surrounding context already attached.