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

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).

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

Explanation:

NOTE: A "temp segment" is not necessarily a SORT segment in a temporary tablespace.

临时段被使用的情形

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

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

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

EXAMPLE 2:

Permanent tablespace INDEX_TBS is being used and has 20gb of space free #此时无法扩展临时表空间的问题当属第2种情形

TIME 1 : Session 1 begins a CREATE INDEX command with the index stored in INDEX_TBS

#下面是Solution部分

There are three recommended methods of supplying sufficient storage space to meet the needs of current sessions by increasing the size of your temporary tablespace

1) Set one or more of the tempfiles|datafiles for the tablespace to AUTOEXTEND with MAXSIZE set ... so that you do not exhaust all available disk volume space

After a month or so of having this setting ... AUTOEXTEND can be disabled .. as it is likely that the system has found its true high watermark for temporary segment usage

(This is the most recommended method as it allows the database instance to find its own high watermark)

2) Monitor the temporary segment usage via queries like

SELECT sum(blocks)*

and resize one or more of the tempfiles|datafiles for the tablespace as the tablespace becomes close to exhausted

3) Add a tempfile|datafile to the temporary tablespace with the problem and monitor usage as described in #2

Another good idea is to monitor temporary tablespace usage over time to determine what queries are consuming the temporary space space

For example: How Can Temporary Segment Usage Be Monitored Over Time (Doc ID 364417.1)

4、本案例故障解决方案