Oracle Error ORA-01652

Error code

ORA-01652

Product

Oracle Database

Versions

Oracle Database 19c, 21c, 23ai and 26ai (wording differs: see below)

Category

Space, Memory & Resource Limits

Last reviewed

24 October 2026

What it means

ORA-01652 means Oracle needed more space for a segment, usually temporary space for a sort, hash join or index build, and the tablespace could not grow to provide it.

Large sorts (ORDER BY, GROUP BY, DISTINCT, analytic functions), hash joins and index builds that do not fit in memory spill to a temporary tablespace. When that tablespace has no free space left and cannot be extended, the statement fails with ORA-01652. Oracle's current reference says this can happen because the tablespace is small or, in a temporary tablespace, because of concurrent use by other sessions; it suggests retrying in case those sessions have released space, then increasing the tablespace if it keeps happening.

The current message names the segment type (temporary, table, index, LOB and so on), the tablespace, the size of the failed allocation, the SQL ID and the temp space the session was already using, which tells you whether one query was the culprit. 19c and 21c print "unable to extend temp segment by N in tablespace X", where N is a number of blocks. If the tablespace named is a permanent one such as USERS, the temporary segment is usually a new index or table being built there; for a table that cannot grow during INSERT or UPDATE see ORA-01653.

Common causes

• A query sorts or hashes far more rows than intended: a missing join condition, a Cartesian product, or a DISTINCT or ORDER BY over a huge intermediate result.
• Many sessions run large sorts at the same time and together use up a temporary tablespace that would be big enough for any one of them.
• The temporary tablespace is small and its tempfiles cannot autoextend, or have reached their MAXSIZE or the disk is full.
• CREATE INDEX, ALTER INDEX ... REBUILD or CREATE TABLE ... AS SELECT builds a large segment in a permanent tablespace that lacks the space.
• A user is assigned to a different (smaller) temporary tablespace than the one you checked.

Diagnose it

Read-only These queries only read the data dictionary (USER_, ALL_ and DBA_ views) and dynamic performance views (V$). They do not change data, privileges or settings, so they are safe to run first. DBA_ and V$ views need the matching privileges.

SELECT tablespace_name,
       ROUND(tablespace_size / 1048576) AS size_mb,
       ROUND(allocated_space / 1048576) AS allocated_mb,
       ROUND(free_space / 1048576)      AS free_mb
FROM dba_temp_free_space;

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

SELECT tablespace_name, file_name,
       ROUND(bytes / 1048576)    AS size_mb,
       autoextensible,
       ROUND(maxbytes / 1048576) AS max_mb
FROM dba_temp_files
ORDER BY tablespace_name, file_name;

-- the temporary tablespace each user gets
SELECT username, temporary_tablespace
FROM dba_users
WHERE username = 'SHOP_APP';

SELECT sql_id, sql_text
FROM v$sql
WHERE sql_id = '7k2m9xq4bz1fd';  -- the SQL ID from the message

How to fix it

Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.

-- filter and aggregate first, then join the small result
SELECT c.name, t.total_amount
FROM (SELECT customer_id, SUM(amount) AS total_amount
      FROM shop.orders
      WHERE order_date >= DATE '2026-01-01'
      GROUP BY customer_id) t
JOIN shop.customers c ON c.customer_id = t.customer_id;

ALTER TABLESPACE temp
  ADD TEMPFILE '/u02/oradata/ORCL/temp02.dbf' SIZE 4G
  AUTOEXTEND ON NEXT 512M MAXSIZE 16G;

-- or grow an existing tempfile
ALTER DATABASE TEMPFILE '/u02/oradata/ORCL/temp01.dbf' RESIZE 8G;

Examples

A missing join condition sorts a Cartesian product

Not reproduced here: no Oracle Database is installed for this site. The result follows the message template in Oracle's current error reference; the segment type spelling, allocation size, SQL ID and MB figure are illustrative. Oracle Database 19c and 21c print "ORA-01652: unable to extend temp segment by N in tablespace TEMP".

SELECT DISTINCT o.order_id, c.name
FROM orders o, customers c          -- no join condition
WHERE o.order_date >= DATE '2026-01-01'
ORDER BY c.name;

ORA-01652: unable to grow temporary in tablespace TEMP by 1MB during operation with SQL ID : 7k2m9xq4bz1fd, temp space used by session : 32767 (MB)

Building a large index in a full permanent tablespace

Not reproduced here: no Oracle Database is installed for this site. This shows the 19c and 21c message format from Oracle's error reference, where the number is a count of blocks and is illustrative. The new index is built as a temporary segment in USERS, so the error names USERS rather than TEMP; current releases print the longer "unable to grow ..." form.

CREATE INDEX orders_customer_ix
  ON orders (customer_id, order_date)
  TABLESPACE users;

ORA-01652: unable to extend temp segment by 1024 in tablespace USERS

How to prevent it

Review execution plans of large reports and batch jobs before they run in production, and look out for Cartesian joins and sorts of huge intermediate results. Spread heavy sorting jobs over time so their temp usage does not peak together.

Monitor DBA_TEMP_FREE_SPACE and V$TEMPSEG_USAGE, size the temporary tablespace for the observed peak with headroom, and use autoextend with a sensible MAXSIZE. Before building large indexes, check free space in the target tablespace.

Common questions

Official documentation

• ORA-01652 (Oracle Database Error Messages)
• V$TEMPSEG_USAGE (Oracle Database Reference 19c)
• DBA_TEMP_FREE_SPACE (Oracle Database Reference 19c)
• Managing Tablespaces (Oracle Database Administrator's Guide 19c)

Last technically reviewed 24 October 2026; examples and diagnostics run on Oracle Database Error Messages reference (26ai, 21c and 19c); not run locally. Messages and explanations follow Oracle's Database Error Messages reference; no Oracle Database was available, so the examples were not run locally.