Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

ORA-1652: unable to extend temp segment tips

Oracle Tips by Burleson Consulting
December 23,  2008

 

Question:  I have been getting the ORA-1652 errors, and I have no more disk to allocate to my TEMP tablespace:

Tue Dec 23 07:38:16 2008
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP
Tue Dec 23 07:51:11 2008
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP

I have waited for SMON to clean out the un-used TEMP segments, and re-starting the instance did not stop the ORA-1652 errors.  It's an emergency.  How do I remove the temp segments?

Answer:  Normally, you would just add disk to TEMP to avoid the ORA-1652 error, but you can also wait for SMON to clean-up the TEMP segment.

See these important notes for fixing the ORA-1652 unable to extend error.

You can check for held TEMP segments with this query:

select
   srt.tablespace,
   srt.segfile#,
   srt.segblk#,
   srt.blocks,
   a.sid,
   a.serial#,
   a.username,
   a.osuser,
   a.status
from
   v$session    a,
   v$sort_usage srt
where
   a.saddr = srt.session_addr
order by
   srt.tablespace, srt.segfile#, srt.segblk#,
   srt.blocks;

 

Oracle docs note this about ORA-01652:

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

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.

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

First ORA-01652 may occur because there is simply no space available in the temp tablespace of which is being used.  The second cause of 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 trouble shoot for ORA-01652,  and find out which of the above scenarios are causing ORA-01652 use this query offered by MOSC:

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, so in the RAC environment, ORA-01652 has to be handled differently.  If you are experiencing ORA-01652 in a non-RA environment, be aware that every SQL making use of the tablespace can fail. 

In RAC, more sort segment space can be used from other instances, which can help resolve ORA-01652 more easily.  Try using the query below:

select inst_id, tablespace_name, total_blocks, used_blocks, free_blocks
from gv$sort_segment;

Basically, you can then find out how much temp segment space can be used for each instance by viewing the total_blocks, and the used_blocks can reveal the space which has been used so far, and the free_blocks gives the amount of space allocated to this particular instance.    This being, to resolve ORA-01652, you can check out that used_blocks = total_blocks and free_blocks = 0 will probably show for the instance, and ORA-01652 will be shown multiple times within the alert log.

This basically means that free space from other instances is being requested, and typically signifies that there is instance contention.  Instance contention within the temporary space can make the instance take more time to process.

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

However, remember to not use the RAC feature of DEFAULT temp space.

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 hanganalyze and systemstate dumps

Here is another example of ORA-01652 from an article regarding ORA-12801.

Question:

I understand ORA-01652 is usually caused by running out of space When I run Oracle parallel query, I keep receiving ORA-01652, why?

Answer:

In this case, there was a sort in the parallel query which continues to cause ORA-01652 to be thrown.  Remember, the parallel query coordinator has receives the returned results from the parallel processes as a last step of the OPQ sort.  This being, you should be able to resolve ORA-01652 by increasing TEMP, and perhaps also the sort_area_size.  For advice on this, refer to the statement below:

 If this job is running batch, you can do this with an alter session command, as this this case, to one gig:

alter session set sort_area_size = 1,048,576,000




 

 

??
 
  
 
 
 

 
 
 

Oracle training Excel
 
Oracle performance tuning software 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.