Edi Yanto (何 萬 新)

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

Posted on: April 19, 2009


Cause: Failed to allocate an extent of the required number of blocks for a temporary segment in the tablespace indicated.

Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

MetaLink has a very detailed and informative article concerning ORA-01652 and RAC.  There is some troubleshooting required with ORA-01652 in RAC because there are two common causes in this area.

  1. ORA-01652 may occur because there is simply no space available in the temp tablespace of which is being used.
  2. ORA-01652 may have to do with the local temp segment not being able to extent space even though there is space in other instances.

To troubleshoot for ORA-01652,  and find out which of the above scenarios are causing ORA-01652 use this query offered by MetaLink:

select sum(free_blocks)
from gv$sort_segment
where tablespace_name = ‘<TEMP TABLESPACE NAME>’

You will know that the first scenario is causing ORA-01652 to be thrown if the free block reads ‘0’ because it signifies that there is no free space.

If there is a good amount of space, you know that there is another cause for ORA-01652, and it is probably the second scenario.  It is important to note that in a non-RAC environment, local instances are not able to extend the temp segments.

In sever cases, a slowdown may occur, in which you might want try one of the following work-arounds:

  1. Increase size of the temp tablespace
  2. Increase sort_area_size and/or pga_aggregate_target

If ORA-01652 is causing the slowdown, SMON will probably not be able to process the sort segment requests, you  should try to diagnose the contention:

  • Output from the following query periodically during the problem:
    select inst_id, tablespace_name, total_blocks, used_blocks, free_blocks
    from gv$sort_segment;
  • Global hang analyze and systemstate dumps

Reference: Oracle Error Tips by Burleson Consulting

4 Responses to "ORA-01652: unable to extend temp segment by 128 in tablespace TEMP"

I have read this article and i have some doubts. First, why you sould increase size of temp tablespace when this tablespace re-use the space, and usualy is always full??

I thought it was used to temp transactions, so in the moment the transaction is finished this space is re-usable. am i wrong??

Thanks!!

Like

Hi Ignacio,

In my case, it completely out of space in the temp tablespace and takes long time for the temp transactions to finish.. it’s a potential workaround for this issue..
Your statement is also right, temp tablespace will reuse the space when the transaction is finished, but how long you can wait?

Thanks..

Edi

Like

Hi,

Very good article. Thanks for providing such great tip.

Like

Hi there I like your post

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,368 other followers


Edi Yanto
Oracle Applications Technical Consultant
View Edi Yanto's profile on LinkedIn

My Certifications






Follow me on Twitter

Archives

April 2009
M T W T F S S
« Mar   May »
 12345
6789101112
13141516171819
20212223242526
27282930  

Visitors

Categories

Blog Stats

  • 140,585 hits

Users Online

web counter

Pages

%d bloggers like this: