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 thoughts on “ORA-1652: unable to extend temp segment by % in tablespace TEMP

  1. 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ı

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.