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

Posted by: Edi Yanto on: April 19, 2009

• In: Oracle
• Comment!

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

Tags: performance, troubleshooting