之前整理过的一篇有关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.
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.
, 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 至少要提供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.
/* 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;