ORA-01652

ORA-01652: unable to extend temp segment by string in tablespace string

Cause: Failed to allocate an extent of the required number of blocks for a temporary segment in the tablespace indicated.

Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated. 

 

From the following solution of above issue, we can select any one as per the oracle database version.

 

Solution 1: You can check for held TEMP segments with this query:

SQL> select b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#, a.username, a.osuser, a.status

from v$session a, v$sort_usage b

where a.saddr = b.session_addr

order by b.tablespace, b.segfile#, b.segblk#, b.blocks;
Use the below command in oracle version 11g to remove a TEMP segment:
SQL>alter tablespace xxxxx coalesce;
SQL>alter tablespace TEMP coalesce;

Solution 2: remove temporary space from a tablespace

Alternatively you can use drop segment event to remove temporary space from tablespace:

ALTER SESSION SET EVENTS 'immediate trace name drop_segments level &x';
where: x is the value for file# from Tablespace.

Solution 3: Add the temp file for Temporary tablespace

SQL>SELECT FILE_NAME||' '||TABLESPACE_NAME||' '||BYTES/1024/1024

 FROM DBA_TEMP_FILES;
FILE_NAME||''||TABLESPACE_NAME||''||BYTES/1024/1024
D:\ORACLE\ORADATA\RMAN\TEMP01.DBF TEMP 40

 
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE ‎‎‘D:\ORACLE\ORADATA\RMAN\TEMP01.DBF’ SIZE 80M;‎
Tablespace altered.

SQL> SELECT TABLESPACE_SIZE/1024/1024||' '||ALLOCATED_SPACE/1024/1024||' ‎‎'||FREE_SPACE/1024/1024 FROM DBA_TEMP_FREE_SPACE;

SQL> SELECT tablespace_name, total_blocks, used_blocks, free_blocks FROM v$sort_segment;

SQL> SELECT tablespace_name, SUM(bytes_used)/1024/1024, SUM(bytes_free)/1024/1024

FROM V$temp_space_header

GROUP BY tablespace_name;

Now, resize or add the datafile related to a Tablespace if require:

SQL> ALTER TABLESPACE rman ADD DATAFILE ‎‎‘D:\ORACLE\ORADATA\RMAN\RTBS02.DBF' SIZE 200M; 

 

SQL> SELECT *FROM DBA_DATA_FILES WHERE TABLESPACE_NAME LIKE '%RT%';

We can resize the existing Datafile if the mount point have enough space
ALTER DATABASE DATAFILE 'D:\ORACLEXE\ORADATA\XE\USERS.DBF' RESIZE ‎‎200M;

 

Solution 4: Create a new Temporary Tablespace and make this temporary tablespace as default temporary tablespace for database.

SQL> CREATE TEMPORARY TABLESPACE TEMP02

TEMPFILE ' ‎D:\ORACLE\ORADATA\RMAN\TEMP02.DBF'

SIZE 256M REUSE AUTOEXTEND ON NEXT ‎‎256M MAXSIZE 8192M

EXTENT MANAGEMENT LOCAL;

Tablespace created.

Now make this temporary tablespace default at Database level:

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP02;

Database altered.

Bounce the database so that actual temporary space is release from 'temp' Tablespace and Drop tablespace 'temp' including content. 

 

SQL> shutdown immediate;

SQL> startup;

SQL> Drop tablespace temp including contents;
Tablespace dropped.

SQL> select *from dba_temp_files;

Now, we can see the new Temporary tablespace is 'TEMP02' with enough free space.