Oracle rebuild index reporting ORA-01652 Solution

A friend said that the rebuild index reported a ORA-01652 error. And temporary tablespace is large enough. The rebuild index is 6 GB, and the remaining space of indextablespace is 2 GB. Ask a friend to increase the index tablespace by 10 Gb. The index is successfully built in rebuild.

A rebuildindex article:

Oracle alter index rebuild description

There are two documents on MOS about this issue: [ID 94178.1] and [ID 120360.1].

When you execute the following command:

Alter index rebuild or

ALTER TABLE MODIFY PARTITION REBUILD LOCALINDEXES

 

A ORA-01652 error may occur, which is explained in detail as follows:

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.

Solution:

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.

-- Add data files to the index tablespace or change the data file to autoextend.

Explanation:

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.

-- Two temporary segments are generated during index rebuild.

The first is the temporarysegments used for sorting. When the SORT_AREA_SIZE value is too small, the segments will be allocated in the user's default temporary tablespace.

The second is the segments in the user's index space. During the rebuild index, a temporary segment is allocated to save the index information. After the rebuild is complete, the old index is droped, the previously assigned temporary segments are defined as permanent segment.

Therefore, to rebuild an index, you must provide at least one time of free space to store temporary segment. Otherwise, an ORA-01652 error occurs.

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 (64 MB 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.

You can check the configuration of the index storageclause to check whether these values are suitable.

/* Formatted on 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;