ORA-01652: 无法将临时段扩展 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<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错误
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)

时间 1：会话 1 启动长时间运行的查询
时间 2：会话 2 启动查询，此时会话 1 已消耗 48GB 的​​ TEMP 可用空间
时间 3：会话 1 和会话 2收到 ORA-1652，因为表空间已耗尽其可用空间
    两个会话都失败..并且会话使用的所有临时空间都被释放（使用的段被标记为“免费”以供重用）
TIME 4 : SMON清理会话 1 和会话 2 使用的临时段（取消分配存储）
TIME 5 ：针对视图 V$SORTSEG_USAGE 或 V$TEMSEG_USAGE 和 V$SORT_SEGMENT 运行查询...发现没有空间正在使用（这是正常的）

示例 2：

永久表空间 INDEX_TBS 正在使用，并且有 20gb 的可用空间 # 此时无法扩展临时表空间的问题当属第 2 种情况