My Oracle Support Banner

How to Cleanup and Rebuild an Interrupted Online Index Rebuild - ORA-8104 , ORA-8106 (Doc ID 272735.1)

Last updated on FEBRUARY 02, 2022

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 to 11.2.0.1 [Release 10.2 to 11.2]
Oracle Database - Enterprise Edition - Version 11.2.0.3 to 11.2.0.3 [Release 11.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Information in this document applies to any platform.


Goal

 

Sometimes an online rebuild of an index reports an ORA-8104.
There can be many reasons for that.

For instance in a highly active database with many transactions the likelyhood of uncommitted transactions on a table is high. So if such a table needs to rebuild an index we may see this error
due to uncommitted transactions. In other cases we may see a dead process with an uncommitted transaction holding a lock on the table. This also prevents us from rebuilding the index. This is
all by design of the online rebuild functionality. However, we can't leave a database in this state forever so SMON has been designed to cleanup these cases.

A process could end up dead if it has been killed from the OS with the command kill -9.

If the process was killed while doing an:
ALTER INDEX <ind> REBUILD ONLINE;
then the index could end up in a state where it needs clearing up.


One would think a drop of the index would solve the problem. However,
that will only return an error:ORA-8104 "This index object %s is being online built or rebuilt."

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Goal
Solution
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.