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

Whilst running an online index rebuild my session terminated abnormally and the session was killed. When trying to rerun the index rebuild statement, the following error occurs:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining and 
Real Application Testing options

SQL> ALTER INDEX myschema.I_MV_CTR_ADVERT_ID rebuild online;
ALTER INDEX myschema.I_MV_CTR_ADVERT_ID rebuild online
*
ERROR at line 1:
ORA-08104: this index object 4145783 is being online built or rebuilt

Cause:

A session failure during an online index rebuild can leave the data dictionary in a state reflecting a rebuild is on going when in fact it is not.

Solution:

The dbms_repair.online_index_clean function has been created to cleanup online index rebuilds. More details about the function on metalink – Session Was Killed During The Rebuild Of Index ORA-08104 [ID 375856.1]

Run the following to resolve:

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;
/
Advertisements

One Response to ORA-08104: this index object 4145783 is being online built or rebuilt

  1. Pingback: ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found « Mohsin's DBA Blog

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: