ORA-01652: unable to extend (grow) temp segment in tablespace

ORA-01652: unable to grow temp segment in tablespace LAB_TMP by 1MB during operation with SQL ID : 105yw6uy4n3gd, temp space used by session : 1 (MB)

A sort or a temporary table needed more temp space than the user's temporary tablespace could give.

Lab-verifiedReproduced and fixed on Oracle AI Database 26ai Free 23.26.3.0.0 · 2026-11-15

Sorts, analytic functions and global temporary tables write to the user's temporary tablespace. When that tablespace has no free space left and cannot grow, the statement stops with ORA-01652. The message names the tablespace (here LAB_TMP). In 23ai it also shows the SQL ID and how much temp space the session had used; older versions say unable to extend temp segment by N in tablespace TEMP.

Check two things first: how big the query really is, and which temp tablespace the user is on (dba_users.temporary_tablespace, dba_temp_free_space). For the lab, the temp tablespace was made 2 MB on purpose.

Causes and fixes

  1. A join condition is missing, so the sort is far bigger than expected
  2. The temporary tablespace is really too small for the work
  3. The user is assigned to a small temp tablespace instead of the default

1A join condition is missing, so the sort is far bigger than expected

An analytic query over orders (20,000 rows) and customers (1,000 rows) forgot WHERE o.cust_id = c.cust_id. Counting the two FROM lists shows the difference: 20,000,000 rows without the join, 20,000 with it. Sorting 20 million rows for ROW_NUMBER() OVER (... ORDER BY ...) ran out of temp space.

With the join condition added, the same query returned 4 rows (one per region, total 500000 each) on the same 2 MB temp tablespace.

Setup
Setuprun as system
DROP TABLESPACE IF EXISTS lab_tmp INCLUDING CONTENTS AND DATAFILES;
CREATE SMALLFILE TEMPORARY TABLESPACE lab_tmp TEMPFILE '/opt/oracle/oradata/FREE/FREEPDB1/lab_tmp01.dbf' SIZE 2M REUSE AUTOEXTEND OFF;
ALTER USER lab_a TEMPORARY TABLESPACE lab_tmp;
OUTPUT
Tablespace dropped.


Tablespace created.


User altered.
Setuprun as lab_a
CREATE TABLE customers (cust_id NUMBER PRIMARY KEY, region VARCHAR2(10));
INSERT INTO customers SELECT level, CASE MOD(level, 4) WHEN 0 THEN 'EAST' WHEN 1 THEN 'WEST' WHEN 2 THEN 'NORTH' ELSE 'SOUTH' END FROM dual CONNECT BY level <= 1000;
CREATE TABLE orders (order_id NUMBER PRIMARY KEY, cust_id NUMBER REFERENCES customers, amount NUMBER);
INSERT INTO orders SELECT level, MOD(level, 1000) + 1, 100 FROM dual CONNECT BY level <= 20000;
COMMIT;
OUTPUT
Table created.


1000 rows created.


Table created.


20000 rows created.


Commit complete.
Reproducerun as lab_a
SELECT region, order_id, total FROM (SELECT c.region, o.order_id, SUM(o.amount) OVER (PARTITION BY c.region) AS total, ROW_NUMBER() OVER (PARTITION BY c.region ORDER BY o.order_id DESC) AS rn FROM orders o, customers c) WHERE rn = 1 ORDER BY region;
OUTPUT
SELECT region, order_id, total FROM (SELECT c.region, o.order_id, SUM(o.amount) OVER (PARTITION BY c.region) AS total, ROW_NUMBER() OVER (PARTITION BY c.region ORDER BY o.order_id DESC) AS rn FROM orders o, customers c) WHERE rn = 1 ORDER BY region
                                                                                                                                                                                                     *
ERROR at line 1:
ORA-01652: unable to grow temp segment in tablespace LAB_TMP by 1MB during operation with SQL ID : 105yw6uy4n3gd, temp space used by session : 1 (MB)
Help: https://docs.oracle.com/error-help/db/ora-01652/
Checkrun as lab_a
SELECT COUNT(*) AS without_join FROM orders o, customers c;
SELECT COUNT(*) AS with_join FROM orders o, customers c WHERE o.cust_id = c.cust_id;
OUTPUT
WITHOUT_JOIN
------------
    20000000

1 row selected.


 WITH_JOIN
----------
     20000

1 row selected.

If ORA-01652 appears on a query that used to work, check its joins before adding space. A Cartesian product will fill any amount of temp.

After the fixrun as lab_a
SELECT region, order_id, total FROM (SELECT c.region, o.order_id, SUM(o.amount) OVER (PARTITION BY c.region) AS total, ROW_NUMBER() OVER (PARTITION BY c.region ORDER BY o.order_id DESC) AS rn FROM orders o, customers c WHERE o.cust_id = c.cust_id) WHERE rn = 1 ORDER BY region;
OUTPUT
REGION       ORDER_ID      TOTAL
---------- ---------- ----------
EAST            19999     500000
NORTH           19997     500000
SOUTH           19998     500000
WEST            20000     500000

4 rows selected.

2The temporary tablespace is really too small for the work

Inserting 20,000 rows of about 200 bytes into a global temporary table needs a few MB, but dba_temp_free_space showed LAB_TMP at 2 MB with 1 MB free.

After ALTER TABLESPACE lab_tmp ADD TEMPFILE '...lab_tmp02.dbf' SIZE 20M, the same INSERT finished: 20000 rows created.

Setup
Setuprun as system
DROP TABLESPACE IF EXISTS lab_tmp INCLUDING CONTENTS AND DATAFILES;
CREATE SMALLFILE TEMPORARY TABLESPACE lab_tmp TEMPFILE '/opt/oracle/oradata/FREE/FREEPDB1/lab_tmp01.dbf' SIZE 2M REUSE AUTOEXTEND OFF;
ALTER USER lab_a TEMPORARY TABLESPACE lab_tmp;
OUTPUT
Tablespace dropped.


Tablespace created.


User altered.
Setuprun as lab_a
CREATE GLOBAL TEMPORARY TABLE tmp_work (id NUMBER, pad VARCHAR2(200)) ON COMMIT PRESERVE ROWS;
OUTPUT
Table created.
Reproducerun as lab_a
INSERT INTO tmp_work SELECT level, RPAD('x', 200, 'x') FROM dual CONNECT BY level <= 20000;
OUTPUT
INSERT INTO tmp_work SELECT level, RPAD('x', 200, 'x') FROM dual CONNECT BY level <= 20000
            *
ERROR at line 1:
ORA-01652: unable to grow temp segment in tablespace LAB_TMP by 1MB during operation with SQL ID : 0fh12364kxdu2, temp space used by session : 1 (MB)
Help: https://docs.oracle.com/error-help/db/ora-01652/
Checkrun as system
SELECT tablespace_name, ROUND(tablespace_size/1024/1024) AS size_mb, ROUND(free_space/1024/1024) AS free_mb FROM dba_temp_free_space ORDER BY tablespace_name;
OUTPUT
TABLESPACE_NAME                   SIZE_MB    FREE_MB
------------------------------ ---------- ----------
LAB_TMP                                 2          1
TEMP                                   10          9

2 rows selected.
Fixrun as system
ALTER TABLESPACE lab_tmp ADD TEMPFILE '/opt/oracle/oradata/FREE/FREEPDB1/lab_tmp02.dbf' SIZE 20M REUSE;
OUTPUT
Tablespace altered.

The lab tablespace was created as SMALLFILE, so a second tempfile could be added. Adding space only helps when the workload is legitimate; check the SQL first.

After the fixrun as lab_a
INSERT INTO tmp_work SELECT level, RPAD('x', 200, 'x') FROM dual CONNECT BY level <= 20000;
SELECT COUNT(*) FROM tmp_work;
COMMIT;
OUTPUT
20000 rows created.


  COUNT(*)
----------
     20000

1 row selected.


Commit complete.

3The user is assigned to a small temp tablespace instead of the default

The same INSERT failed in LAB_TMP. dba_users showed LAB_A's temporary tablespace was LAB_TMP (2 MB), while the database default (DEFAULT_TEMP_TABLESPACE) was TEMP with 9 MB free.

ALTER USER lab_a TEMPORARY TABLESPACE temp; moved the user to the default one, and the INSERT then created 20000 rows.

Setup
Setuprun as system
DROP TABLESPACE IF EXISTS lab_tmp INCLUDING CONTENTS AND DATAFILES;
CREATE SMALLFILE TEMPORARY TABLESPACE lab_tmp TEMPFILE '/opt/oracle/oradata/FREE/FREEPDB1/lab_tmp01.dbf' SIZE 2M REUSE AUTOEXTEND OFF;
ALTER USER lab_a TEMPORARY TABLESPACE lab_tmp;
OUTPUT
Tablespace dropped.


Tablespace created.


User altered.
Setuprun as lab_a
CREATE GLOBAL TEMPORARY TABLE tmp_work (id NUMBER, pad VARCHAR2(200)) ON COMMIT PRESERVE ROWS;
OUTPUT
Table created.
Reproducerun as lab_a
INSERT INTO tmp_work SELECT level, RPAD('x', 200, 'x') FROM dual CONNECT BY level <= 20000;
OUTPUT
INSERT INTO tmp_work SELECT level, RPAD('x', 200, 'x') FROM dual CONNECT BY level <= 20000
            *
ERROR at line 1:
ORA-01652: unable to grow temp segment in tablespace LAB_TMP by 1MB during operation with SQL ID : 0fh12364kxdu2, temp space used by session : 1 (MB)
Help: https://docs.oracle.com/error-help/db/ora-01652/
Checkrun as system
SELECT username, temporary_tablespace FROM dba_users WHERE username = 'LAB_A';
SELECT CAST(property_value AS VARCHAR2(30)) AS default_temp FROM database_properties WHERE property_name = 'DEFAULT_TEMP_TABLESPACE';
SELECT tablespace_name, ROUND(tablespace_size/1024/1024) AS size_mb, ROUND(free_space/1024/1024) AS free_mb FROM dba_temp_free_space ORDER BY tablespace_name;
OUTPUT
USERNAME                       TEMPORARY_TABLESPACE
------------------------------ ------------------------------
LAB_A                          LAB_TMP

1 row selected.


DEFAULT_TEMP
------------------------------
TEMP

1 row selected.


TABLESPACE_NAME                   SIZE_MB    FREE_MB
------------------------------ ---------- ----------
LAB_TMP                                 2          1
TEMP                                   10          9

2 rows selected.
Fixrun as system
ALTER USER lab_a TEMPORARY TABLESPACE temp;
OUTPUT
User altered.

The tablespace name in the message tells you which one ran out. If it is not the one you expected, look at the user's setting.

After the fixrun as lab_a
INSERT INTO tmp_work SELECT level, RPAD('x', 200, 'x') FROM dual CONNECT BY level <= 20000;
SELECT COUNT(*) FROM tmp_work;
COMMIT;
OUTPUT
20000 rows created.


  COUNT(*)
----------
     20000

1 row selected.


Commit complete.

FAQ

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

A sort, analytic function or temporary table needed more space in the temporary tablespace named in the message, and the tablespace could not grow.

How do I check free space in the temp tablespace?

SELECT tablespace_name, tablespace_size, free_space FROM dba_temp_free_space; shows size and free bytes for each temp tablespace.

Which temp tablespace does my user use?

SELECT temporary_tablespace FROM dba_users WHERE username = 'USER';. The database default is in database_properties under DEFAULT_TEMP_TABLESPACE.

Should I just add a tempfile when I get ORA-01652?

Check the query first. In the lab, a missing join condition turned 20,000 rows into 20,000,000; fixing the join was enough without adding space.

How we verify

Each scenario starts from fresh database users. A script runs the setup, confirms the exact error code, applies the fix and runs the statement again. The output shown is what the database printed, saved by the script.

lab_a and lab_b are ordinary users created for the test; system is the DBA account. Oracle AI Database 26ai Free Release 23.26.3.0.0 - Develop, Learn, and Run for Free