// Oracle error reference

ORA-01652: unable to extend temp segment

A sort, hash join or temporary table needed more space in the temporary tablespace than it could get. Usually a query that sorts far more data than it should.

Error code
ORA-01652
Category
Space, memory and limits
Error message
unable to extend temp segment by string in tablespace string

What it means

Large sorts, hash joins, GROUP BY, index builds and global temporary tables spill to the temporary tablespace. ORA-01652 means that tablespace ran out of room for this operation. The full 19c message is unable to extend temp segment by string in tablespace string. One runaway query (often a missing join condition producing a Cartesian product) can use all of it.

Newer releases add the SQL ID and how much temp space the session used.

Common causes

  • A query with a missing or wrong join condition that multiplies rows.
  • A large sort or hash join on more data than expected, for example due to a bad plan.
  • The temporary tablespace is simply too small for the workload, or shared by many concurrent big queries.
  • Temp files with autoextend off or at their maximum size.

How to fix it

  1. Find who is using temporary space now.

    SELECT s.sid, s.username, s.sql_id, u.tablespace, u.blocks
    FROM v$tempseg_usage u JOIN v$session s ON s.saddr = u.session_addr
    ORDER BY u.blocks DESC;
  2. Check the query's plan and join conditions before adding space.

  3. If the workload genuinely needs more, add a temp file or let it grow.

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

Related errors

More space, memory and limits errors

The code and message text are from the Oracle Database Error Help. Browse the full Oracle error list or search every database error.

// fewer errors, faster fixes

Work on Oracle in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for Oracle and the other databases you use.