How to Resolve ORA-08104: this index object is being online built or rebuilt

ORA-08104

We interrupted an index rebuilding online statement to make more resource available for online transactions. After a while, we tried to redo the statement, but it failed with ORA-08104.


SQL> ALTER INDEX ERPAPP.PAY_IX1 REBUILD ONLINE PARALLEL 8;

ALTER INDEX ERPAPP.PAY_IX1 REBUILD ONLINE PARALLEL 8

*

ERROR at line 1:

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

It seems that something was left over after the failed rebuild.

To recover the situation, we should clean up all failed index rebuilding, either manually or automatically.

DBMS_REPAIR.ONLINE_INDEX_CLEAN

We can clean up all qualified objects or a specific one. Here we use a function to fix it.

ALL_INDEX_ID

declare
v_result boolean;
begin
v_result := dbms_repair.online_index_clean();
end;
/

Leaving all parameters empty defaults to ALL_INDEX_ID which means all objects qualified to clean up, and LOCK_WAIT which means trying getting DML locks on underlying table.

Please refer to Package SYS.DBMS_REPAIR, Function ONLINE_INDEX_CLEAN

Specific OBJECT_ID

For cleaning up specific OBJECT_ID only, we can provide the problematic ID:

v_result := dbms_repair.online_index_clean(185720);

Wait SMON to Clean Up

The second option is to wait, waiting for System Monitor (SMON) to clean up the incomplete temporary segment. It should be no more than 60 minutes.

Now you can try your actions again.