🧩 How to Resolve ORA-01652: Unable to Extend Temp Segment by XXX in Tablespace TEMP

🧩 Introduction:

The ORA-01652 error occurs when Oracle cannot allocate space in the TEMP tablespace to execute a query. This typically happens during large sorts, joins, or index creation. Resolving this issue ensures queries and DML operations execute without interruptions.

Step 1: Check TEMP Tablespace Usage

Determine how much TEMP space is used:

SELECT tablespace_name, file_name, bytes/1024/1024 AS size_mb

FROM dba_temp_files;

Also, monitor current usage:

SELECT tablespace_name, SUM(bytes_used)/1024/1024 AS used_mb

FROM v$temp_space_header

GROUP BY tablespace_name;

Step 2: Add Space to TEMP Tablespace

If the TEMP tablespace is full, add a new tempfile:

ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/TEMP02.dbf' 

RESIZE 500M;

-- or

ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/TEMP03.dbf' 

ADD SIZE 500M;

Step 3: Optimize Queries

Large sorts or joins may consume excessive TEMP space. Consider:

Breaking queries into smaller chunks.

Using indexes to reduce sort operations.

Using CREATE GLOBAL TEMPORARY TABLE for intermediate results.

Step 4: Example Scenario

A user runs:

SELECT * FROM EMPLOYEES e JOIN DEPARTMENTS d ON e.DEPT_ID = d.DEPT_ID

ORDER BY e.LAST_NAME;

If TEMP space is insufficient, ORA-01652 occurs. Adding a tempfile or optimizing the query avoids the error.

Step 5: Preventive Measures

Regularly monitor TEMP tablespace usage.

Set autoextend on TEMP files to prevent future space issues:

ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/TEMP01.dbf' AUTOEXTEND ON MAXSIZE UNLIMITED;

Conclusion:

ORA-01652 errors indicate insufficient TEMP tablespace. By monitoring usage, increasing TEMP space, and optimizing queries, DBAs can ensure smooth execution of large operations in Oracle databases.