ORA-01652: Unable to extend temp segment by 128 in tablespace TEMP in Oracle Database

If you receive this error, this may indicate a problem with the underlying application packages.

Determine the status of the TEMP tablespace

Log in as the oracle user and connect to the database as user SYS using the command below or by using SQL Developer.

sqlplus "/as sysdba"

Run the queries below to determine current TEMP tablespace sizing and free space.

This query lists the overall size in total and how much is being used in MB:

SELECT   d.tablespace_name,
         NVL(a.bytes / 1024 / 1024, 0) Total_in_MB ,
         NVL(t.bytes, 0)/1024/1024 Used_in_MB
FROM     sys.dba_tablespaces d,
         (SELECT tablespace_name, SUM(bytes) bytes, COUNT(file_id) COUNT
         FROM dba_temp_files
         GROUP BY tablespace_name) a,
         (SELECT ss.tablespace_name ,
         SUM((ss.used_blocks*ts.blocksize)) bytes
         FROM gv$sort_segment ss, sys.ts$ ts
         WHERE ss.tablespace_name = ts.name
         GROUP BY ss.tablespace_name) t
WHERE    d.tablespace_name = a.tablespace_name(+)
AND      d.tablespace_name   = t.tablespace_name(+)
AND      d.tablespace_name LIKE 'TEMP%';

If the queries above show that TEMP tablespace is full and/or each segment has reached limit, then increase TEMP tablespace size as a workaround.

Execute the following SQL

• ASM partitioning:

ALTER TABLESPACE "TEMP" ADD TEMPFILE '+DG01' SIZE 2G AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED; 
COMMIT;

• local filesystem:

ALTER TABLESPACE "TEMP" ADD TEMPFILE '/u01/app/oracle/oradata/AVDB/tempXX.dbf' 
SIZE 2G AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED; 
COMMIT;

where the 'XX' in 'tempXX.dbf' is the next sequential number available. To see the already existing TEMPFILES:

SQL> select file_name, bytes/1024/1024 from dba_temp_files;