How to Fix ORA-01652: Unable to Extend Temp Segment in Oracle

ORA-01652 means a statement needed more space in a tablespace than it could get. Most often the tablespace is the temporary one, used by sorts, hash joins, and index builds that do not fit in memory; the tablespace is full and its files cannot grow. Oracle AI Database 26ai's message says "unable to grow temp segment", with the tablespace, the size requested, and the SQL ID. Give the tablespace room, or make the statement need less.

Reproduce the Error

A test user gets a 2 MB temporary tablespace that cannot grow:

Example:

-- a small temporary tablespace that cannot grow
drop user if exists demo_app cascade;
drop tablespace if exists demo_small_temp including contents and datafiles;
create temporary tablespace demo_small_temp
  tempfile '/opt/oracle/oradata/FREE/FREEPDB1/demo_small_temp01.dbf' size 2m autoextend off;
create user demo_app identified by "Demo_Pass_26" temporary tablespace demo_small_temp;
grant create session to demo_app;

A large sort with little sort memory has to spill to it:

Example:

sqlplus -s -L demo_app/Demo_Pass_26@localhost:1521/FREEPDB1 <<'SQL'
-- small sort memory, so a large sort spills to the temporary tablespace
alter session set workarea_size_policy = manual;
alter session set sort_area_size = 65536;
select max(rn) as rows_sorted
from  (select row_number() over (order by pad desc, n) as rn
       from  (select rownum as n, rpad('x', 200, 'x') as pad
              from   all_objects a cross join all_objects b
              where  rownum <= 200000));
SQL

Output:

Session altered.

Session altered.

              from   all_objects a cross join all_objects b
                     *
ERROR at line 4:
ORA-01652: unable to grow temp segment in tablespace DEMO_SMALL_TEMP by 1MB
during operation with SQL ID : 2tqgt2hp491vw, temp space used by session : 1
(MB)

Find the Space

Example:

select tablespace_name, tablespace_size / 1024 / 1024 as size_mb,
       free_space / 1024 / 1024 as free_mb
from   dba_temp_free_space where tablespace_name = 'DEMO_SMALL_TEMP';
select autoextensible, bytes / 1024 / 1024 as mb from dba_temp_files
where  tablespace_name = 'DEMO_SMALL_TEMP';

Output:

TABLESPACE_NAME       SIZE_MB    FREE_MB
__________________ __________ __________
DEMO_SMALL_TEMP             2          1

AUTOEXTENSIBLE       MB
_________________ _____
NO                    2

Fix: Let the Temp File Grow

Example:

-- let the temp file grow, up to a limit
alter database tempfile '/opt/oracle/oradata/FREE/FREEPDB1/demo_small_temp01.dbf'
  autoextend on next 16m maxsize 512m;
select autoextensible, bytes / 1024 / 1024 as mb, maxbytes / 1024 / 1024 as max_mb
from   dba_temp_files where tablespace_name = 'DEMO_SMALL_TEMP';

Output:

Database altered.

AUTOEXTENSIBLE       MB    MAX_MB
_________________ _____ _________
YES                   2       512

The same sort now completes:

Output:

Session altered.

Session altered.

ROWS_SORTED
-----------
     200000

Other Fixes

• Find the sessions and statements using temp space in V$TEMPSEG_USAGE, by SQL ID; one runaway query is often the cause.
• Tune the statement: a missing join condition or an unneeded ORDER BY or DISTINCT can create huge sorts.
• For a smallfile tablespace, ADD TEMPFILE adds space; a bigfile tablespace, the default in Oracle AI Database 26ai, has one file that must grow instead.
• When the tablespace in the message is a permanent one, such as USERS, the space ran out during CREATE TABLE AS SELECT or an index build: add space to that tablespace.

Related Guides

• How to Check Table Space Usage with DBMS_SPACE
• How to Fix ORA-01950: No Privileges on Tablespace in Oracle

Conclusion

ORA-01652 means a tablespace, usually TEMP, ran out of space for a statement. Check its size and free space, let its files grow to a sensible limit, and find and tune the statements that use the most temp space.