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
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
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;Tablespace dropped.
Tablespace created.
User altered.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;Table created.
1000 rows created.
Table created.
20000 rows created.
Commit complete.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;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/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;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.
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;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
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;Tablespace dropped.
Tablespace created.
User altered.CREATE GLOBAL TEMPORARY TABLE tmp_work (id NUMBER, pad VARCHAR2(200)) ON COMMIT PRESERVE ROWS;Table created.INSERT INTO tmp_work SELECT level, RPAD('x', 200, 'x') FROM dual CONNECT BY level <= 20000;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/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;TABLESPACE_NAME SIZE_MB FREE_MB
------------------------------ ---------- ----------
LAB_TMP 2 1
TEMP 10 9
2 rows selected.ALTER TABLESPACE lab_tmp ADD TEMPFILE '/opt/oracle/oradata/FREE/FREEPDB1/lab_tmp02.dbf' SIZE 20M REUSE;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.
INSERT INTO tmp_work SELECT level, RPAD('x', 200, 'x') FROM dual CONNECT BY level <= 20000;
SELECT COUNT(*) FROM tmp_work;
COMMIT;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
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;Tablespace dropped.
Tablespace created.
User altered.CREATE GLOBAL TEMPORARY TABLE tmp_work (id NUMBER, pad VARCHAR2(200)) ON COMMIT PRESERVE ROWS;Table created.INSERT INTO tmp_work SELECT level, RPAD('x', 200, 'x') FROM dual CONNECT BY level <= 20000;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/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;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.ALTER USER lab_a TEMPORARY TABLESPACE temp;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.
INSERT INTO tmp_work SELECT level, RPAD('x', 200, 'x') FROM dual CONNECT BY level <= 20000;
SELECT COUNT(*) FROM tmp_work;
COMMIT;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