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

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

****************I am authoring a Book on Oracle Database Cloud Services.......More details to follow.***************

Title : Oracle Database Cloud Revealed
Publisher : Apress
Release Date : Jan-2019

**********************************************************************************


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]


My Cloud Certifications:
Oracle Cloud Infrastructure 2018 Certified Architect Associate.
Oracle Cloud Infrastructure Classic 2018 Certified Architect Associate.
Oracle Database Cloud Administrator Certified Professional.
Oracle Database Cloud Service Operations Certified Associate.

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