ORA-01652: unable to extend temp segment

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

A sort, hash join or similar operation ran out of temporary tablespace. Very often the real cause is a bad execution plan, not a small tablespace. Find the statement using TEMP, fix it, and only then add space.

What ORA-01652 means

Operations that do not fit in memory spill to the temporary tablespace: large sorts, hash joins, index creation, global temporary tables. If the space runs out, the statement fails with ORA-01652. A cartesian join caused by a missing join condition can consume hundreds of gigabytes in minutes.

Common causes of ORA-01652

• A query with a bad plan or a missing join condition.
• A very large sort, DISTINCT, GROUP BY or hash join.
• Index builds or large CREATE TABLE AS SELECT operations.
• Many sessions using TEMP at the same time, or a TEMP that is simply too small.

How to fix ORA-01652

1. Find who uses TEMP now: V$TEMPSEG_USAGE joined to V$SESSION.
2. Review the plan of the heaviest statement; look for missing join conditions and cartesian products.
3. If the space is really needed, add a tempfile: ALTER TABLESPACE temp ADD TEMPFILE ….
4. Check that the message names TEMP. If it names a permanent tablespace, the operation was building a permanent segment there.

ORA-01652 example

Who is using TEMP right now?

SELECT s.sid, s.username, u.sql_id, u.tablespace,
       ROUND(u.blocks * t.block_size / 1024 / 1024) AS 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 mb DESC;

Add temporary space only if it is really needed:

ALTER TABLESPACE temp ADD TEMPFILE SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 30G;

In SQLlightspeed

When a statement fails, SQLlightspeed’s error panel shows the error code with a plain-language explanation and a suggestion for what to do next, together with Oracle’s own message and a link to Oracle’s documentation — for this error and dozens of other common ones.

Related errors

ORA-01653ORA-01555ORA-04031 All Oracle errors →

Oracle documentation for ORA-01652. Oracle is a registered trademark of Oracle Corporation. SQLlightspeed is not affiliated with Oracle.