alter index sameer.myindex rebuild online
*
ERROR at line 1:
ORA-08104: this index object 59081 is being online built or rebuilt
As long as the index is not rebuild all access to the index will result in ORA-8104 or ORA-8106.
In case you are not performing the DBMS_REPAIR.ONLINE_INDEX_CLEAN operation , SMON will eventually cleanup the locked index so no actions are actually needed. However, letting SMON do the cleanup can be a bit of 'hit and miss' as SMON will try to cleanup every 60 minutes and if it cannot get a lock on the object with NOWAIT it will just try again later. In a highly active database with many transactions this can cause the rebuild to take a long time as SMON won't get the lock with NOWAIT. Other cases like uncommitted transactions against the table will also result in SMON not rebuilding the index.
Thus, you have 2 options:
1. Let SMON automatically performing the cleanup
or
SQL> select obj#,flags from ind$ where obj#=75350;
OBJ# FLAGS
---------- ----------
59081 3742
2. Run DBMS_REPAIR.ONLINE_INDEX_CLEAN. This operation is faster, but will put load on the system.
declare
isclean boolean;
begin
isclean :=false;
while isclean=false
loop
isclean := DBMS_REPAIR.ONLINE_INDEX_CLEAN(dbms_repair.all_index_id,dbms_repair.lock_wait);
dbms_lock.sleep(10);
end loop;
end;
/
SQL> select obj#,flags from ind$ where obj#=75350;
OBJ# FLAGS
---------- ----------
59081 3251
If the current load on the system is not affected but this broken index, I would suggest you to wait the quiet period of the database and then run DBMS_REPAIR.ONLINE_INDEX_CLEAN(dbms_repair.all_index_id,dbms_repair.lock_wait);.
