Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Friday, September 28, 2012

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



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);.

1 comment:

Unknown said...

Thanks for this just got woken from a slew ora 25027's because some online rebuild fell over and a dbms_stats job