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

------------------------------- ------------ ----------- -----------
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:
  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


  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

  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ı