ORA-01652: unabletoextendtempsegmentby8192...

The latest error message "unabletoextendtempsegmentby8192intablespace..." was prompted during rebuildindex. This is a common error. Index in

The latest error message "unable to extend temp segment by 8192 in tablespace..." was prompted during rebuild index. This is a common error. Index in

The latest error message "unable to extend temp segment by 8192 in tablespace..." was prompted during rebuild index. This is a common error. When creating an index, you need to use the user's default temporary tablespace for sorting and generating temporary segments in the index tablespace. If the current index tablespace limits automatic expansion or has reached the maximum value of the data file, this error message appears. The following describes the specific analysis and solutions.

Recommended reading:

ORA-01172, ORA-01151 error handling

ORA-00600 [2662] troubleshooting

Troubleshooting for ORA-01078 and LRM-00109

Notes on ORA-00471 Processing Methods

ORA-00314, redolog corruption, or missing Handling Methods

Solution to ORA-00257 archive logs being too large to store

1. error message

Alter index err ORA-01652: unable to extend temp segment by 8192 in tablespace
GX_ARCHIVE_IDX
DECLARE
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 8192 in tablespace GX_ARCHIVE_IDX
ORA-06512: at line 90

# The following information comes from alert log
Sun Mar 30 03:08:51 2014
ORA-1652: unable to extend temp segment by 128 in tablespace GX_ARCHIVE_IDX
ORA-1652: unable to extend temp segment by 8192 in tablespace GX_ARCHIVE_IDX

# Fault Environment
SQL> select * from v $ version where rownum <2;

BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.3.0-64bit Production

SQL> ho cat/etc/issue

Welcome to SUSE Linux Enterprise Server 10 SP4 (x86_64)-Kernel \ r (\ l ).

2. ORA-1652 errors
Error: ORA-1652
Text: unable to extend temp segment by % s in tablespace % s
------------------------------------------------------------------------------
Cause: Failed to allocate an extent for temp segment in tablespace.
Action: Use alter tablespace add datafile statement to add one or more
Files to the tablespace indicated or create the object in another
Tablespace.

* ** Important: The notes below are for experienced users-See Note: 22080.1

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.

NOTE: A "temp segment" is not necessarily a SORT segment in a temporary tablespace.
It is also used for temporary situations while creating or dropping objects like tables and indexes in permanent tablespaces.
Eg: When you perform a create index a TEMP segment is created to hold what will be the final permanent index data.
This TEMP segment is converted to a real INDEX segment in the dictionary at the end of the create index operation.
It remains a temp segment for the duration of the create index operation and so failures to extend
It report ORA-1652 rather than an INDEX related space error.

Use of temporary segments
A temporary segment may be from:
1) a sort Used for a SELECT or for DML/DDL
2) create index The index create performs a SORT in the users default TEMP tablespace and ALSO uses a TEMP segment to build the final index in the INDEX tablespace.
Once the index build is complete the segment type is changed.
3) CREATE PK CONSTRAINT
4) ENABLE CONSTRAINT
5) create table New tables start out as TEMPORARY segments.
Eg: If MINEXTENTS is> 1 or you issue CREATE table as SELECT.
6) Accessing a global temporary table When you access a global temporary table a TEMP segment is instantiated to hold the temporary data.

3. TROUBLESHOOTING ORA-01652 (Reference Doc ID 1267351.1)

# The following are two scenarios where temporary segments cannot be extended:
EXAMPLE 1:

Temporary tablespace TEMP is being used and is 50 gb in size (a recommended minimum for 11g)

TIME 1: Session 1 starts a long running query
TIME 2: Session 2 starts a query and at this point in time Session 1 has consumed 48 gb of TEMP's free space
TIME 3: Session 1 and Session 2 receive a ORA-1652 because the tablespace has exhausted of its free space
Both sessions fail .. and all temp space used by the sessions are freed (the segments used are marked FREE for reuse)
TIME 4: SMON cleans up the temporary segments used by Session 1 and Session 2 (deallocates the storage)
TIME 5: Queries are run against the views V $ SORTSEG_USAGE or V $ TEMSEG_USAGE and V $ SORT_SEGMENT... and it is found that no space is being used (this is normal)

EXAMPLE 2:

Permanent tablespace INDEX_TBS is being used and has 20 gb of space free # the problem that temporary tablespace cannot be expanded at this time is 2nd Cases