Thursday, February 27, 2014

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

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





2 comments:

  1. Thank you! saved us during an ongoing issue

    ReplyDelete