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

Posted: June 14, 2012 in Objects

Got this error after session was killed whilst doing a index rebuild and then trying to rebuild the index again, its seems the cause is – A session failure during an online index rebuild can leave the data dictionary in a state reflecting a rebuild is ongoing when in fact it is not – see metalink note: 375856.1 for more details

Error
TEST SQL> alter index TEST.TEST_DETAIL_1 rebuild online;
alter index TEST_TEST.AUDIT_DETAIL_1 rebuild online
*
ERROR at line 1:
ORA-08104: this index object 75192 is being online built or rebuilt

 

Fix the Issue

Run DBMS_REPAIR.ONLINE_INDEX_CLEAN to fix the issue so the index can be rebuilt

 

TEST SQL> declare
2 isclean boolean;
3 begin
4 isclean :=false;
5 while isclean=false
6 loop
7 isclean := DBMS_REPAIR.ONLINE_INDEX_CLEAN(dbms_repair.all_index_id,dbms_repair.lock_wait);
8 dbms_lock.sleep(10);
9 end loop;
10 end;
11 /

PL/SQL procedure successfully completed.

 

Now Rebuild the Index
TEST SQL> alter index BOAUD_OWNER.AUDIT_DETAIL_1 rebuild online;

Index altered.

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s