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
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
-
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;
-
Check the query's plan and join conditions before adding space.
-
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
ORA-01653unable to extend table in tablespaceORA-30036unable to extend segment in undo tablespaceORA-04031unable to allocate bytes of shared memory
More space, memory and limits errors
ORA-01950no privileges on tablespaceORA-01555snapshot too oldORA-01000maximum open cursors exceededORA-00018maximum number of sessions exceededORA-00020maximum number of processes exceededORA-00257archiver error
The code and message text are from the Oracle Database Error Help. Browse the full Oracle error list or search every database error.
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.