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).
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;
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
There are two ways of solving this error:
- Add more tempfiles, increase the size of the current ones or enable auto extend and the most important:
- Tune the queries/statements so that the sort operations are done in memory and not on the disk.