ORA-1652: unable to extend temp segment by 128 in tablespace TEMP

Error:
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP Fri Jul 11 01:09:12 2010

What does that error means:
This error is fairly self explanatory - we cannot get enough space for a temporary segment. The size reported in the error message is the number of contiguous free Oracle blocks that cannot be found in the listed tablespace.

How to approach the ORA-1652 error

There are two views in the database that help keep track of the free space in the temporary tablespace: v$sort_usage and v$tempseg_usage (from 9i onwards).

Check the status of the sort segment utilization :

select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS from v$sort_segment; TABLESPACE_NAME                 TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS ------------------------------- ------------ ----------- ----------- TEMP                                 1310592           0     1310592

If USED_BLOCKS = TOTAL_BLOCKS, find which user and statement is using the temporary sort segment by following;

How Do You Find Who And What SQL Is Using Temp Segments

SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text FROM v$session a, v$tempseg_usage b, v$sqlarea c WHERE a.saddr = b.session_addr AND c.address= a.sql_address AND c.hash_value = a.sql_hash_value ORDER BY b.tablespace, b.blocks;

Resolution

There are two ways of solving this error:

1. Add more tempfiles, increase the size of the current ones or enable auto extend and the most important:

2. Tune the queries/statements so that the sort operations are done in memory and not on the disk.