ORA-01652: unable to extend temp segment by 8192...

最近在rebuild index时提示unable to extend temp segment by 8192 in tablespace..的错误提示。这个是个比较常见的错误。索引在

最近在rebuild index时提示unable to extend temp segment by 8192 in tablespace..的错误提示。这个是个比较常见的错误。索引在创建的时候需要使用到该用户缺省的临时表空间进行排序，以及在索引表空间生成临时段。如果当前的索引表空间限制了自动扩展或者已经达到了数据文件的最大值，此错误提示便会出现。下面是具体的分析及其解决过程。

推荐阅读：

ORA-01172、ORA-01151错误处理

ORA-00600 [2662]错误解决

ORA-01078 和 LRM-00109 报错解决方法

ORA-00471 处理方法笔记

ORA-00314,redolog 损坏，或丢失处理方法

ORA-00257 归档日志过大导致无法存储的解决办法

1、错误提示信息

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

#下面的信息来自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

#故障环境
SQL> select * from v$version where rownum

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错误
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.

临时段被使用的情形
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)

#下面是无法扩展临时段的2种情形
EXAMPLE 1:

Temporary tablespace TEMP is being used and is 50gb 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 48gb of TEMP's free space
TIME 3 : Session 1 and Session 2 receive an ORA-1652 because the tablespace has exhausted of 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 20gb of space free #此时无法扩展临时表空间的问题当属第2种情形