SQL> ALTER INDEX TEST.IDX_01 rebuild online; ALTER INDEX TEST.IDX_01 rebuild online * ERROR at line 1: ORA-08104: this index object 8945783 is being online built or rebuilt
In order to resolve this issue you have to use function dbms_repair.online_index_clean as sysdba. This is a fix that is installed with patch for BUG:3805539 and if you get an error calling the function , then please install it.
SQL>set serveroutput on
SQL>declare
isclean boolean;
begin
isclean := false;
while isclean=false
loop
isclean := dbms_repair.online_index_clean(object_id=>dbms_repair.all_index_id, wait_for_lock=>dbms_repair.lock_wait
);
dbms_lock.sleep(10);
end loop;
exception
when others then
raise;
end;
/
Parameter input:
object_id :
ALL_INDEX_ID -> cleanup all index that qualify
[OBJECT_ID] -> cleanup the specified index by using the object_id
wait_for_lock :
LOCK_WAIT -> retry getting DML locks on underlying table
LOCK_NOWAIT -> do not retry getting DML locks on underlying table
Returns:
TRUE -> specified indexe(s) were cleaned up successfully
FALSE -> one or more indexes failed cleaned up but its possible that some may have been cleaned up successfully.
REFERENCES
Oracle Support Doc ID 375856.1 & 1378173.1