ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
Posted April 19, 2009on:
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.
- ORA-01652 may occur because there is simply no space available in the temp tablespace of which is being used.
- 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:
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:
- Increase size of the temp tablespace
- 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
- Global hang analyze and systemstate dumps
Reference: Oracle Error Tips by Burleson Consulting