ORA-1652 Error Troubleshooting [ID 793380.1]
		修改时间 12-JUL-2010 类型 TROUBLESHOOTING 状态 PUBLISHED

In this Document
Purpose
Last Review Date
Instructions for the Reader
Troubleshooting Details
References

---

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.8 to 11.2.0.1.0 - Release: 9.2 to 11.2
Information in this document applies to any platform.

Purpose

This document can be used to approach the 'ORA-1652: unable to extend temp segment by %s in tablespace %s' error in non-RAC environments.

For RAC environments, use Note 280578.1 - Troubleshooting ORA-1652 Errors in RAC.

Last Review Date

August 8, 2009

Instructions for the Reader

Troubleshooting Details

Error explanation

This error is fairly self explanatory - we cannot get enough space for a temporary segment. The size reported in the error message is the number of contiguous free Oracle blocks that cannot be found in the listed tablespace.

How to approach the ORA-1652 error

There are two views in the database that help keep track of the free space in the temporary tablespace: v$sort_usage and v$tempseg_usage (from 9i onwards). In order for the approach to be relevant, the following investigation steps should be followed when the error occurs (i.e. as soon as the problem is spotted and before any sql performing a sort finishes) :

1. Check the status of the sort segment utilization :

   select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS from v$sort_segment;
2. If USED_BLOCKS = TOTAL_BLOCKS, find which user and statement is using the temporary sort segment by following:
   Note 317441.1 - How Do You Find Who And What SQL Is Using Temp Segments
3. In some cases, you may find that the ORA-1652 is not reported for a temporary tablespace, but a permanent one. This is not an abnormal behaviour and it can occur for example while creating or dropping objects like tables and indexes in permanent tablespaces. Reference : Note 19047.1 - OERR: ORA 1652 unable to extend temp segment by %s in tablespace %s
   In such cases the following note will be of use :
   Note 100492.1 - ORA-01652: Estimate Space Needed to CREATE INDEX
4. If the tablespace in which the TEMPORARY segment resides is of type PERMANENT, also check that the following events are not set in the initialization parameter file:
   
   event="10061 trace name context forever, level 10"
   event="10269 trace name context forever, level 10"
   
   If they are set, unset them and restart database.
   
   These two events prevent SMON from cleaning up.
   
   Reference : Note 1039341.6 - Temporary Segments Are Not Being De-Allocated After a Sort

In the process of diagnostic and tuning, the resumable statement feature can be useful. It allows the DBA, once having applied the appropriate solution to the space allocation issue, to resume the suspended transaction which does not lose all the work done previously. By querying dba_resumable one can find the statement that is executed when ORA-1652 occurs. See Note 136941.1 - Using RESUMABLE Session to Avoid Transaction Abort Due to Space Errors.

There are two ways of solving this error:

1. Add more tempfiles, increase the size of the current ones or enable auto extend and the most important:
2. Tune the queries/statements so that the sort operations are done in memory and not on the disk.

Note that the extents allocated for a user's sort segment are NOT deallocated but are marked as FREE from performance reasons. The FREE extents can be further used by other users that are executing sort
operations. After the database restart the allocated extents are NOT released either but are FREE to be reused.
Hence, seeing the physical space of the temporary tablespace fully allocated is not a reason to be concerned per se. The above query of V$SORT_SEGMENT should be used to establish the free space inside the temporary tablespace.

Known issues

Note 463819.1 - Database HANG After Migrating to 10.2 : ORA-1652
Note 164850.1 - ORA-01652 in Resumable Statements Prevents any SELECT on DBA_RESUMABLE View
Note 750209.1 - Temp LOB space not released after commit: ora-1652 being hit

References

NOTE:1039341.6 - Temporary Segments Are Not Being De-Allocated After a Sort
NOTE:164850.1 - ORA-01652 in Resumable Statements Prevents any SELECT on DBA_RESUMABLE View
NOTE:280578.1 - Troubleshooting ORA-1652 Errors in RAC
NOTE:317441.1 - How Do You Find Who And What SQL Is Using Temp Segments
NOTE:463819.1 - Database HANG After Migrating to 10.2 : ORA-1652
NOTE:750209.1 - Temporary LOB space not released after commit: ora-1652 being hit

相关的 产品 Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition 关键字 V$SORT_USAGE; V$TEMPSEG_USAGE; TEMPORARY SEGMENT; TEMPFILES; TEMPORARY TABLESPACE; RESUMABLE; TABLESPACE FREE; FREE SPACE 错误 ORA-1652; 1652 ERROR

[@more@]