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

First check Alert.log file of database; Error was related to RMAN backup.

Step 1: Run below command on problem target database if SQL statement is hang or come out with Temporary tablespace error then Check the Temporaray tablespace usage.

SQL> select count(*) from v$rman_status ;select count(*) from v$rman_status;
ERROR at line 1:ORA-01652: unable to extend temp segment by 640 in tablespace TEMP

Step 2: Find out which User/Users are taking the space on temporaray tablespace and which is the SQL satement.

SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, (b.blocks*8*1024/(1024*1024)) as MB, c.sql_textFROM v$session a, v$tempseg_usage b, v$sqlarea cWHERE a.saddr = b.session_addrAND c.address= a.sql_addressAND c.hash_value = a.sql_hash_valueORDER BY b.tablespace, b.blocks
/

select tablespace, username, segtype, sum(blocks*8*1024/(1024*1024)) as mb, count(*) as count from v$tempseg_usage group by tablespace, username, segtype
/

Step3: If it is SYS user account taking too much of temporaray tablespace space then. Please follow the follwing Metalink notes:

To implement the solution on Target database., please execute the following steps:

SQL> exec dbms_stats.gather_fixed_objects_stats;

Oracle Document;
Uses a lot Of Temporary Segments ORA-1652: Unable To Extend Temp Segment
Doc ID: Note:357765.1
Type: PROBLEM
Last Revision Date: 02-APR-2007
Status: MODERATED