Oracle rebuild index 报ORA-01652 解决办法

Oracle rebuild index 报ORA-01652 解决办法

一朋友就说rebuild index 报ORA-01652错误。并且temporary tablespace 是足够大的。 rebuild 索引是6G，indextablespace 剩余空间是2G。让朋友把index tablespace 空间增加10G，在rebuild index成功。

之前整理过的一篇有关rebuildindex 的文章：

Oracle alter index rebuild 说明

关于这个问题MOS 上有2篇文档进行了相关的说明：[ID 94178.1] 和 [ID 120360.1]。

执行如下命令时：

ALTER INDEX REBUILD or

ALTER TABLE MODIFY PARTITION REBUILD LOCALINDEXES

 

可能会遇到ORA-01652的错误， 该错误详细解释如下：

            ORA-01652:unable to extend temp segment by %s in tablespace %s

            Cause:  Failed to allocate an extent for temp segmentin tablespace.

            Action:Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to thetablespace indicated or create the object in other              tablespace.

解决方法：

You will have to increase the amount of available free space in the index tablespaceeither by adding another datafile or enabling autoextend on an existingdatafile.

--增加索引表空间添加数据文件或者将数据文件改成autoextend。

解释：

Thetablespace in the error message is pointing to the index's tablespace insteadof the user's default temporary tablespace.

 

During an index rebuild, there are two types oftemporary segments involved.

           First, there are the temporarysegments that are used to store partial sort data when the SORT_AREA_SIZE istoo small to process the complete sort set. These segments are built in theuser's default TEMPORARY tablespace.

            Second, as the index is being rebuilt, it uses a segment which is defined as atemporary segment until the rebuild is complete. Once this segment is fully populated,the old index can be dropped and this temporary segment is redefined as apermanent segment with the index name.

--在索引rebuild 期间，会产生2种temporary segments。

第一种是用来排序的temporarysegments,当SORT_AREA_SIZE 的值太小时，该segments 会在用户默认的temporary tablespace 空间分配。

第二种是在用户索引空间的segments，在rebuild index时，会分配一个temporary segment 来保存索引的信息，当rebuild 结束之后，old index 被droped 掉，之前分配的temporary segments 会定义为permanent segment。

所以对索引进行rebuild 至少要提供1倍以上的空闲空间来存放temporary segment。 否则就会出现ORA-01652的错误。

The error you are seeing is probably due to there being insufficient room in theindex's tablespace to hold both the original index and the new version concurrently.The new version of the index, currently a temp segment, will be in thetablespace where the index is required.

As an index is being rebuilt, it uses a segment which is defined as a temporarysegment for the rebuild. Once this segment is fully populated, the allocationof the old index is set to temporary and the populated temporary segment isredefined as a permanent segment with the index name.

Now if the storage clause (next extent for example) for the existing index is setto a very high number (64MB or so) and you don't specify a storage clause withthe rebuild command Oracle will use the storage clause of the existing index toallocate the space for the temporary segments.

This will allocate a lot of (unneeded) space and so you will run into the ora-1652.

可以检查索引storageclause的配置，看这些值是否合适。  

/* Formatted on 2011/8/3 12:27:36(QP5 v5.163.1008.3004) */

SELECT OWNER,

       INDEX_NAME,

      TABLESPACE_NAME,

      INITIAL_EXTENT,

       NEXT_EXTENT,

       MIN_EXTENTS,

       MAX_EXTENTS,

       PCT_INCREASE

  FROM dba_indexes;