The open-orders report has run every morning for a year. Today it fails:

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

The fix everyone knows is one line: add a tempfile. It takes a minute, it’s online, and the next run fails again, a little later. So someone adds a bigger tempfile, turns on autoextend, and the report fails a third time, after filling the disk.

ORA-01652 says TEMP ran out. It doesn’t say why. Sometimes TEMP really is too small for legitimate work. Often, one query is asking for far more TEMP than any database could give it, and the error is the only thing standing between you and a wrong answer. This post covers what TEMP is for, how to tell the two cases apart, how to find the query, and why TEMP looks full even when it isn’t. It ends with a lab where giving TEMP ten times the space buys ten times the runtime and the same error, while restoring one join predicate makes the same report need no TEMP at all.

What TEMP is for

Sorts, hash joins, DISTINCT, GROUP BY, analytic functions and index builds all need a work area. Oracle gives each one memory from the PGA first. When the work area outgrows its share of memory, the operation spills to the temporary tablespace: it writes partial results to TEMP, then reads them back to finish.

Spilling is normal; a big nightly batch or an index rebuild is supposed to use TEMP. ORA-01652 is raised when a spilling operation asks for another extent and the temporary tablespace has none left to give, and can’t grow. The statement fails and its TEMP is released.

So the question is never just “is TEMP big enough?” It’s “is this operation’s demand reasonable?” A sort of 200,000 rows that needs 40 MB of TEMP is reasonable. A sort that needs 8 GB to produce 50,000 rows is a bug.

The classic runaway

Here is the report as it shipped:

SELECT DISTINCT c.name, c.region, o.order_id, o.amount
FROM   orders o, customers c
WHERE  o.status = 'OPEN';

A line went missing in an edit: AND o.customer_id = c.customer_id. Without it, there is no join condition, so Oracle pairs every open order with every customer, a Cartesian product. The report suddenly showed each order thousands of times, so someone added DISTINCT to “remove the duplicates.”

Now the database has to deduplicate the whole product. With 50,000 open orders and 2,000 customers, that’s 100 million rows into a HASH UNIQUE, far beyond any PGA work area. It spills, and keeps spilling until TEMP is gone.

And here’s the part that makes adding space the wrong fix: the rows aren’t actually duplicates. Each customer name is different, so every one of the 100 million combinations is distinct. Given enough TEMP, the report would finish, and return 100 million rows instead of 50,000. ORA-01652 is the only thing stopping a wrong report from shipping.

⤢ Enlarge

Prove it

The lab runs Oracle Database Free in a container. It creates a 32 MB temporary tablespace that cannot autoextend and assigns it to an application user. That user owns ORDERS (200,000 rows, 50,000 of them OPEN) and CUSTOMERS (2,000 rows). Then it runs the broken report three ways:

TEMP            report                     result            elapsed   TEMP used
32 MB           join predicate missing     ORA-01652          1.2 s       31 MB
320 MB (10x)    join predicate missing     ORA-01652 again   10.7 s      318 MB
32 MB           join predicate restored    50,000 rows        0.1 s        0 MB

Ten times the space bought ten times the runtime, then the same error. (Elapsed times depend on the hardware; the lab checks the outcomes, not the clock.) A hundred times the space would buy a hundred times the runtime. With the join predicate back, and DISTINCT left in place to show that the join alone is the fix, the report returns its 50,000 rows in a tenth of a second without touching TEMP.

The plan says so before anything runs. EXPLAIN PLAN on the broken report:

| Id  | Operation               | Name      | Rows  | Bytes |TempSpc|
---------------------------------------------------------------------
|   3 |    HASH UNIQUE          |           |  200M |  6484M|  8445M|
|   4 |     MERGE JOIN CARTESIAN|           |  200M |  6484M|       |
|   5 |      TABLE ACCESS FULL  | ORDERS    |  100K |  1660K|       |
|   6 |      BUFFER SORT        |           |  2000 | 34000 |       |
|   7 |       TABLE ACCESS FULL | CUSTOMERS |  2000 | 34000 |       |

MERGE JOIN CARTESIAN and a TempSpc estimate of 8,445M, about 8 GB of TEMP, for a report that should return 50,000 rows. (The optimizer estimates twice the real row count because, without a histogram, it assumes half the orders are OPEN. That doesn’t change the conclusion.) Reading an execution plan for TempSpc and Cartesian joins takes seconds, and it would have caught this before the report ever ran.

Don’t take my word for it — run it. The temp-space lab runs the broken report on 32 MB of TEMP and asserts ORA-01652. It asserts the Cartesian join in the plan, then adds a 288 MB tempfile online and asserts the same error again, after catching the report live in V$TEMPSEG_USAGE. Finally it drops the extra tempfile and asserts that the corrected report returns 50,000 rows using no TEMP. If any of that doesn’t reproduce, the run fails. Proven on every CI push.

How to find the query

Read the whole message. Recent releases put the culprit in the error itself. The lab’s Oracle Database Free (26ai) says grow rather than extend, and names the SQL ID and how much TEMP the session held:

ORA-01652: unable to grow temp segment in tablespace TEMP_SMALL by 1MB during operation
with SQL ID : 249gkzhhu3cdk, temp space used by session : 31 (MB)

The same line goes to the alert log. On 19c the message names only the tablespace, so you have to catch the statement another way.

Live: V$TEMPSEG_USAGE. While the statement is running, this view shows who holds TEMP and for which SQL:

SELECT s.sid, s.username, u.sql_id_tempseg, 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 u.blocks DESC;

Use SQL_ID_TEMPSEG, not SQL_ID. In the lab, watching the report from a second session, SQL_ID pointed at SQL*Plus’s own login query (SELECT DECODE(USER, 'XS$NULL', ...), while SQL_ID_TEMPSEG was the report that was actually holding the TEMP. The other catch: once the statement fails, its TEMP is released and the row is gone. In the lab, V$TEMPSEG_USAGE is empty a second after the error.

After the fact: ASH. Active Session History samples every active session once a second, and each sample records TEMP_SPACE_ALLOCATED. The statement that filled TEMP stands out:

SELECT sql_id, COUNT(*) AS samples,
       ROUND(MAX(temp_space_allocated) / 1024 / 1024) AS peak_temp_mb
FROM   v$active_session_history
WHERE  sample_time > SYSTIMESTAMP - INTERVAL '30' MINUTE
AND    temp_space_allocated > 0
GROUP  BY sql_id
ORDER  BY peak_temp_mb DESC;

In the lab, ASH caught 249gkzhhu3cdk peaking at over 300 MB of TEMP. For older incidents, DBA_HIST_ACTIVE_SESS_HISTORY has the same column, and an AWR report shows the top SQL for the window. (ASH and AWR are part of the Diagnostics Pack on Enterprise Edition.)

Then look at its plan. With the SQL ID, DBMS_XPLAN.DISPLAY_CURSOR (if the cursor is still in the shared pool) or EXPLAIN PLAN on the statement text tells you whether the demand makes sense. Compare the TempSpc and row estimates with what the statement should return.

Why TEMP looks full when it isn’t

Right after the failure, the lab looks at the 32 MB tablespace through four views:

V$TEMPSEG_USAGE        0 rows        the session that caused it is gone
V$TEMP_SPACE_HEADER    32 MB used, 0 MB free
V$SORT_SEGMENT         0 MB in use
DBA_TEMP_FREE_SPACE    31 MB free of 32 MB

Oracle doesn’t hand TEMP extents back to the files when a sort finishes. It keeps them allocated in the sort segment and gives them to the next operation that needs them, which is faster than allocating fresh ones. V$TEMP_SPACE_HEADER keeps counting those extents as used after the sort ends, so a monitoring check built on it reports TEMP at 100% all day after the first big sort. That isn’t a leak, and it isn’t a reason to add space. V$SORT_SEGMENT (USED_BLOCKS) and DBA_TEMP_FREE_SPACE show what’s actually free. Alert on those.

The fix

If the SQL is wrong, fix the SQL. A missing join predicate, a DISTINCT hiding duplicates, a join on the wrong column, a plan that hash-joins two huge row sources because statistics are stale: these need a code or statistics fix. TEMP is just where the damage shows up.

If the work is legitimate, add space online. Big index builds, a nightly aggregation, and more concurrent users than TEMP was sized for are real reasons TEMP is too small:

-- Smallfile temporary tablespace: add a tempfile
ALTER TABLESPACE temp ADD TEMPFILE '/u01/oradata/PROD/temp02.dbf' SIZE 8G;

-- Bigfile temporary tablespace (one file only): resize it
ALTER DATABASE TEMPFILE '/u01/oradata/PROD/temp01.dbf' RESIZE 16G;

Both are online, and so is ALTER TABLESPACE ... DROP TEMPFILE once nothing is using the file; the lab adds a 288 MB tempfile and drops it again without a restart. A bigfile tablespace holds exactly one file, so ADD TEMPFILE fails with ORA-32771 there. The lab’s 26ai database creates new tablespaces as bigfile unless told otherwise; check DEFAULT_TBS_TYPE in DATABASE_PROPERTIES on yours.

Contain the damage.

• Cap autoextend. An autoextending tempfile without MAXSIZE doesn’t fix a runaway query. It lets the query fill the filesystem instead of the tablespace, and then everything on that filesystem fails too.
• Separate temporary tablespaces. Give a reporting or batch user its own temporary tablespace (the lab’s user has TEMP_SMALL). Then one application’s runaway query can’t starve every other session’s sorts.
• Size from evidence. The peak of DBA_HIST_ACTIVE_SESS_HISTORY.TEMP_SPACE_ALLOCATED summed across sessions, over a few busy weeks, tells you what your legitimate workload actually needs.

What teams get wrong

• Adding space first, asking later. It’s the right fix maybe half the time. Check the statement’s plan before you add the second tempfile, not after the third.
• Turning on unlimited autoextend. It converts a failed report into a full filesystem. Use MAXSIZE.
• Trusting V$TEMP_SPACE_HEADER. It shows extents the sort segment has cached, not space in use. A TEMP that “is always 100% full” is usually fine.
• Looking for the culprit too late, or in the wrong column. V$TEMPSEG_USAGE empties the moment the statement fails; read the full error text, or go to ASH. While it’s running, trust SQL_ID_TEMPSEG, not SQL_ID.
• Hiding duplicates with DISTINCT. Unexpected duplicates are a symptom of a wrong join. DISTINCT makes the report look right on small data and turns into a TEMP bomb on real data.
• Confusing it with a permanent tablespace. ORA-01652 ... in tablespace USERS (or any non-temporary tablespace) is a different problem: a CREATE TABLE AS SELECT, index build or MOVE ran out of room while building its new segment. The tablespace named in the message tells you which case you have.

Frequently asked questions

What does ORA-01652 unable to extend temp segment mean?

It means an operation that spills to disk, such as a sort, hash join, DISTINCT, GROUP BY or index build, asked the temporary tablespace for another extent and none was available, and the tablespace could not grow. The statement fails and its temporary space is released. The error says that TEMP ran out, not why: either the temporary tablespace is genuinely too small for legitimate work, or one statement is demanding far more TEMP than it should, often because of a missing join predicate or a bad plan.

Should I just add a tempfile to fix ORA-01652?

Only after checking the statement that caused it. If the work is legitimate, such as a large index build, a nightly batch or more concurrent sessions, adding a tempfile (or resizing the tempfile of a bigfile tablespace) is the right fix and it is online. If the statement is a runaway, such as a Cartesian join behind a DISTINCT, more space only moves the failure. In the companion lab, giving TEMP ten times the space made the same report fail again after ten times the runtime, while restoring one join predicate made it return 50,000 rows using no TEMP.

How do I find which query caused ORA-01652?

Recent Oracle releases include the SQL ID and the session's temp usage in the error message and the alert log. On 19c, which names only the tablespace, query V$TEMPSEG_USAGE joined to V$SESSION while the statement is running (use its SQL_ID_TEMPSEG column, which identifies the statement that owns the temp segment; SQL_ID can show a different statement the session parsed), or look in Active Session History afterwards: V$ACTIVE_SESSION_HISTORY and DBA_HIST_ACTIVE_SESS_HISTORY record TEMP_SPACE_ALLOCATED for every sample, so grouping by SQL_ID and taking the maximum shows the statement that filled TEMP. Then check its execution plan for a Cartesian join or a TempSpc estimate far larger than the result should need.

Why does my temporary tablespace always show 100% used?

Because Oracle keeps temporary extents allocated in the sort segment after a sort finishes and reuses them for the next operation, rather than freeing them back to the tempfiles. V$TEMP_SPACE_HEADER counts those cached extents as used, so it can show TEMP as full long after the sorts that used it have finished. V$SORT_SEGMENT (USED_BLOCKS versus FREE_BLOCKS) and DBA_TEMP_FREE_SPACE show the space that is actually free, and are the right views to monitor.

Can I add or drop a tempfile without downtime?

Yes. ALTER TABLESPACE temp ADD TEMPFILE ... SIZE ... is online, and so is ALTER TABLESPACE temp DROP TEMPFILE ... once no session is using the file. A bigfile temporary tablespace can hold only one tempfile, so adding a file fails with ORA-32771; resize the existing one with ALTER DATABASE TEMPFILE ... RESIZE instead. DATABASE_PROPERTIES.DEFAULT_TBS_TYPE shows whether new tablespaces default to bigfile or smallfile on your database.

Is ORA-01652 on a permanent tablespace the same problem?

No. When the message names a permanent tablespace such as USERS rather than a temporary one, the space was needed to build a new permanent segment: a CREATE TABLE AS SELECT, an index build or rebuild, or an ALTER TABLE MOVE, which create their new segment as temporary extents in the target tablespace until they finish. The fix is space in that tablespace, or a smaller operation, not a bigger TEMP.

ORA-01652 is easy to make go away for a day and hard to make go away for good, because the obvious fix treats the symptom. Before you add space, read the whole error, find the statement, and look at its plan. If the demand is legitimate, add a tempfile online and cap its growth. If it’s a Cartesian join behind a DISTINCT, no amount of TEMP will help, and enough of it would let a wrong report finish. It belongs with the other errors that freeze or fail work for a reason the message doesn’t spell out, like ORA-01555 and ORA-00257. Then prove it the way that ends the argument — with the temp-space lab, where ten times the TEMP buys ten times the runtime and the same error, and one restored join needs none.