Session Was Killed During The Rebuild Of Index ORA-8104 [ID 375856.1]

While running an online index rebuild your session was killed or otherwise terminated abnormally.  You are now attempting to run the index rebuild again and is throwing the error:

ORA-08104: this index object ##### is being online built or rebuilt

A session failure during an online index rebuild can leave the data dictionary in a state reflecting a rebuild is ongoing when in fact it is not.


The dbms_repair.online_index_clean function has been created to cleanup online index rebuilds. Use the dbms_repair.online_index_clean function to resolve the issue.

* Please note if you are unable to run the dbms_repair.online_index_clean function it is due to the fact that you have not installed the patch for Bug 3805539 or are not running on a release that includes this fix.  The fix for this bug is a new function in the dbms_repair package called dbms_repair.online_index_clean, which has been created to cleanup online index [[sub]partition] [re]builds. New functionality is not allowed in patchsets; therefore, this is not available in a patchset but is available in 10gR2.

Un comentariu:

  1. For our refer.

    For some busy DB, DBMS_REPAIR.ONLINE_INDEX_CLEAN will failed to be executed ( command executed successfully but in-journal table cannot be released)

    you have to lock the associated table in exclusive mode:

    LOCK TABLE OWNER.TABLE in exclusive mode;

    Meanwhile, open another session to query your "lock table" session which session is locking the table.

    you need to keep killing the FINAL_BLOCK_SESSION until exclusive lock can be acquired.