ORA-08104 after a session has been killed during an online index creation/rebuild

Killing a session that is creating/rebuilding an index online rebuild your session was killed and you trying to drop the index or re-creat/rebuild you get
ORA-08104: this index object ##### is being online built or rebuilt
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

Leave a Reply

Your email address will not be published. Required fields are marked *