ORA-01652 hits your alert log and a batch job, an ETL run, or an analytical query dies mid-execution. Sessions report ORA-01652: unable to extend temp segment by N in tablespace TEMP. Throughput to the rest of the database may be fine, but anything that needs work space on disk is now blocked.

The temp tablespace is the overflow for PGA. Sorts, hash joins, bitmap operations, global temporary tables (GTTs), and temporary LOBs all allocate here when work does not fit in per-process memory. Unlike a permanent tablespace full event, temp full does not usually mean the database is down. It means a specific class of query cannot run.

The diagnostic path: find which session holds the temp, find out why (sort, hash, LOB, GTT), then decide whether to add space, kill the consumer, fix the SQL, or right-size PGA.

What this means

When a sort or hash join exceeds the work area Oracle assigns to the session, the overflow goes to the temp tablespace. As long as free temp space exists, queries spill and keep running, just slower. When temp is exhausted, the next allocation fails with ORA-01652.

Two things make this tricky:

  • Temp is not returned to the OS. Oracle keeps freed extents in the sort segment pool for reuse. DBA_TEMP_FREE_SPACE.FREE_SPACE reflects space available for new allocations, not space handed back to the filesystem. A “full” temp may stay visually full after consumers finish, until ALTER TABLESPACE ... SHRINK SPACE or SMON cleanup reclaims it.
  • Some consumers hold temp until disconnect. Sort and hash segments free when the operation completes, but global temporary tables and temporary LOBs persist for the life of the session. A long-lived connection pool session that builds temp LOBs in a loop can accumulate temp without an active query.

In RAC, each instance manages its own sort segment within the shared temp tablespace, so usage is uneven across nodes. V$TEMPSEG_USAGE is instance-local; query GV$TEMPSEG_USAGE for the cluster view.

Common causes

CauseWhat it looks likeFirst thing to check
PGA undersized for the workloaddirect path read temp and direct path write temp waits dominate; many sessions spillingV$PGASTAT cache hit percentage and over allocation count
Runaway query (Cartesian join, missing join condition, plan regression)One or two sessions hold most of temp; BUFFER_GETS/EXEC far above baseline for the SQL_IDV$SQL for the SQL_ID in V$TEMPSEG_USAGE, compare plan hash to baseline
Global temporary tables and temporary LOBsTemp stays high after queries finish; SEGTYPE is LOB_DATA, LOB_INDEX, or DATAV$TEMPSEG_USAGE filtered by SEGTYPE, plus session connect time
Temp tablespace simply undersizedTemp fills during normal peak, even with reasonable consumersDBA_TEMP_FREE_SPACE trend and high-water mark in V$SORT_SEGMENT
Concurrent analytical load spikeMultiple moderate consumers add up at the same instantSum of top-N V$TEMPSEG_USAGE consumers vs free space

Quick checks

Run these read-only queries as soon as ORA-01652 appears.

-- Tablespace-level temp 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;

-- Top session consumers right now
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;  -- 12c+; on 11g use WHERE ROWNUM <= 20

-- Sort segment pool health per temp tablespace
SELECT TABLESPACE_NAME, CURRENT_USERS, TOTAL_EXTENTS, USED_EXTENTS, FREE_EXTENTS
FROM V$SORT_SEGMENT;

-- Are sessions spilling from PGA?
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');

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

-- Recent ORA-01652 in the alert log
SELECT originating_timestamp, message_text
FROM V$DIAG_ALERT_EXT
WHERE message_text LIKE '%ORA-01652%'
ORDER BY originating_timestamp DESC
FETCH FIRST 20 ROWS ONLY;  -- 12c+; on 11g use WHERE ROWNUM <= 20
<!-- TODO: verify V$DIAG_ALERT_EXT availability and column names across 11g/12c/19c -->

How to diagnose it

  1. Confirm temp is actually the limit. Check DBA_TEMP_FREE_SPACE. If FREE_SPACE is near zero and you are seeing ORA-01652 in the alert log, the diagnosis is confirmed. If FREE_SPACE is non-trivial, the failing operation may be hitting MAXSIZE on a single tempfile or a quota issue, not tablespace exhaustion.
  2. Find the biggest consumer. The V$TEMPSEG_USAGE query above orders sessions by temp consumption. The top few usually account for most of the pressure. Note the SEGTYPE: SORT and HASH indicate active operations, while LOB_DATA, LOB_INDEX, and DATA suggest persistent temp from GTTs or temp LOBs.
  3. Tie the consumer to a SQL_ID and check the plan. For SORT or HASH consumers, pull the SQL text from V$SQL and look at BUFFER_GETS / EXECUTIONS and the current PLAN_HASH_VALUE. Compare against history if you have it. A sudden jump in gets per execution after a stats gathering job is the classic plan regression signature.
  4. Correlate with PGA spill. If direct path read temp and direct path write temp are dominant waits and V$PGASTAT.cache hit percentage is below 80%, the workload is spilling because PGA is undersized for the concurrent analytical load. A growing over allocation count means Oracle is exceeding PGA_AGGREGATE_TARGET to keep work in memory.
  5. Check for session-pinned temp. If V$TEMPSEG_USAGE shows LOB or DATA segments held by sessions that are INACTIVE or in SQL*Net message from client, the temp is held for the life of the session. Killing the session is the only way to free it without waiting for disconnect.
  6. Verify autoextend is real. Cross-check DBA_TEMP_FILES for AUTOEXTENSIBLE, BYTES, and MAXBYTES. A tempfile added with BYTES = MAXSIZE provides no growth headroom even though autoextend looks on.
flowchart TD
    A[Temp near full + ORA-01652] --> B[Top V TEMPSEG_USAGE consumer]
    B --> C{SEGTYPE}
    C -->|SORT, HASH| D[Active spill]
    C -->|LOB_DATA, DATA| E[Session-pinned temp]
    D --> F{Plan regression?}
    F -->|Yes| G[SQL Plan Baseline]
    F -->|No| H[Add temp or raise PGA]
    E --> I[Kill session or shorten pool life]

Metrics and signals to monitor

SignalWhy it mattersWarning sign
DBA_TEMP_FREE_SPACE.FREE_SPACEReal available temp before ORA-01652Trending toward zero during normal peak
V$TEMPSEG_USAGE top consumersIdentifies the session and SQL to fix or killOne session holding most of temp
direct path read temp / direct path write temp wait timePGA spill indicatorBecoming a top wait, correlated with rising temp
V$PGASTAT.cache hit percentageHow often work area requests are satisfied in memoryBelow 80% sustained
V$PGASTAT.over allocation countOracle is exceeding PGA_AGGREGATE_TARGETGrowing counter
V$PGASTAT.total PGA allocated vs PGA_AGGREGATE_LIMITDistance to the hard PGA cap (12c+)Approaching 80% of limit
V$SORT_SEGMENT used vs total extentsLive sort segment pressureUSED_EXTENTS close to TOTAL_EXTENTS
ORA-01652 entries in alert logDirect evidence of failed allocationsAny occurrence in production

Fixes

Add space to the temp tablespace

Fastest unblock. Add a tempfile or resize an existing one.

-- Add a tempfile (adjust path and size for your environment)
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/oradata/PROD/temp02.dbf'
  SIZE 4G AUTOEXTEND ON NEXT 256M MAXSIZE 32G;

This buys time but does not fix the root cause. If the consumer is a runaway query or a leaking session, temp will fill again.

Reclaim freed but cached temp

After consumers finish, freed extents stay in the sort segment pool. Shrink the tablespace to give space back to the filesystem.

-- Reclaim unused allocated temp (11g+)
ALTER TABLESPACE TEMP SHRINK SPACE;

This is non-disruptive to active sorts but may not reclaim everything if any session holds extents.

Right-size PGA to reduce spill

If direct path read temp is a dominant wait and PGA has headroom against physical RAM, raising PGA_AGGREGATE_TARGET (the soft target) keeps more work in memory.

ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 16G SCOPE = BOTH;

Pair this with a sane PGA_AGGREGATE_LIMIT (12c+) as a hard cap to prevent runaway sessions from triggering the OOM killer. Peak total PGA allocated should stay below roughly 80% of PGA_AGGREGATE_LIMIT. If you are still on WORKAREA_SIZE_POLICY = MANUAL with SORT_AREA_SIZE and HASH_AREA_SIZE, switch to AUTO and let Oracle size work areas dynamically.

Kill the offending session

When one session is clearly the problem and cannot be paused, terminate it.

-- Disruptive: ends the session immediately
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

Sort and hash segments free on kill. GTT and temp LOB segments also free at disconnect.

Fix the SQL

If the root cause is a plan regression or a missing join condition, the durable fix is at the SQL layer.

  • For plan regressions: capture the known-good plan with DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE to lock it via SQL Plan Baselines.
  • For missing join conditions and Cartesian products: rewrite the query.
  • For queries that legitimately need large work areas: schedule them outside the OLTP peak, or move them to an Active Data Guard standby.

Prevention

  • Trend DBA_TEMP_FREE_SPACE and V$SORT_SEGMENT high-water marks. Sizing decisions need a high-water mark over weeks, not a single point. Alert at 80% of capacity.
  • Alert on direct path read temp and direct path write temp as leading indicators. Spill starts before temp fills.
  • Monitor V$PGASTAT.over allocation count and cache hit percentage. Over-allocation growing or cache hit below 80% means PGA is undersized for the workload.
  • Use SQL Plan Baselines for analytical SQL that touches temp. Plan regressions are the most common avoidable cause of temp exhaustion.
  • Isolate analytical workloads. Run reports on a physical standby or a separate PDB with its own resource plan so a single query cannot starve OLTP work areas.
  • Watch connection pool lifetime for temp LOB and GTT users. Long-lived sessions that build temp LOBs in a loop accumulate temp silently until disconnect.

How Netdata helps

  • Per-second DBA_TEMP_FREE_SPACE tracking exposes the slope of temp consumption before it hits the cliff, instead of a single polled snapshot.
  • Correlate temp growth with direct path read temp and direct path write temp waits in one view to confirm PGA spill as the driver.
  • V$PGASTAT trends for total PGA allocated, over allocation count, and cache hit percentage show whether the fix is more PGA or fewer concurrent analytical sessions.
  • Top V$TEMPSEG_USAGE consumers by SQL_ID and SEGTYPE let you triage SORT and HASH consumers separately from LOB_DATA and GTT accumulators.
  • ML anomaly detection on temp and PGA signals flags unusual spill patterns even when absolute thresholds look fine.

Netdata’s Oracle Database monitoring with Netdata brings these signals together with per-second metrics and ML anomaly detection.