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-01652 tips

Oracle Error Tips by Burleson Consulting

Question:  What is the cause of the ORA-01652 error?

Answer:  The Oracle oerr utility note this about the ORA-01652 error:

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.

First, see these important notes for fixing the ORA-1652 unable to extend error

If you have purchased the packs to use the AWR, see dba_hist_undostat tips , which can help unveil how Oracle attempted to used all available undo before aborting with the ORA-01652 error.

This sill add space t the TEMP tablespace:

alter tablespace
   temp
add tempfile
'e:\oracle\app\oradata\zoom\temp02.dbf' size 1g
autoextend on;

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
   see code depot for full scripts
   v$session    a,
   v$sort_usage srt
where
   a.saddr = srt.session_addr
order by
   srt.tablespace, srt.segfile#, srt.segblk#,
   srt.blocks;

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


 

 

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.


 

��  
 
 
 
 

 
 
 

 
 
Oracle performance tuning software 
 
oracle dba poster
Oracle Linux poster