Sunday, August 5, 2012

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

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).

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

Conclusion

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.
 Thank you
Osama Mustafa

2 comments:

  1. I pay a quick visit each day some websites and blogs to read content, but this blog gives feature based articles.
    Stop by my blog lose weight quick

    ReplyDelete
  2. Hеllo my fаmily member! I wish to say that this post is amazіng, nice written anԁ include аlmoѕt all
    signifiсant іnfos. Ι would like tο
    look еxtгa posts like this .

    Heге is my webpage Gram altın fiyatı

    ReplyDelete