Index Online Rebuild Fails with ORA-08104: this index object <#> is being online built or rebuilt".

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Symptoms

Index online rebuild fails with error "ORA-08104: this index object 556463 is being online built or rebuilt".

Cause

First online rebuild attempt had failed.  Until SMON performs the clean up, subsequent rebuilds against the index will fail with ORA-8104 or ORA-8106.

Solution

DBMS_REPAIR.ONLINE_INDEX_CLEAN() is only available starting in release 10.2

For 10.1, you need to wait for SMON process to finish the cleanup job.

Note: SMON will perform the cleanup and does this once every 60 minutes. SMON cleanup is only successful if there are no transactions against the base table [or [sub]partition] at the time of the attempted cleanup.  In an environment where there are likely to be uncommitted transactions, this makes cleanup a bit 'hit and miss'.  To speed up the process, you can stop your application which uses the table and wait until the cleanup is done.

Get Oracle Certifications for all Exams
Free Online Exams.com

How to use DBMS_REPAIR.ONLINE_INDEX_CLEAN ?

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Goal

When you try to rebuild an index online for the second time, after terminating the first session you tried to rebuild the index online, you get the following error : 

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

Fix

To resolve this issue you should refer to the following method run the rebuild using DBMS_REPAIR.ONLINE_INDEX_CLEAN function:

--Parameters--:

object_id : ALL_INDEX_ID (= 0) : cleanup all index objects that qualify

> 0 : cleanup sepcified index object

wait_for_lock : LOCK_WAIT=1 : retry getting DML locks on underlying table [[sub]partition] object.

There is an internal retry limit, after which the lock get will give up

LOCK_NOWAIT=0: do not retry

--Returns--:

TRUE : all indexes specified were cleaned up

FALSE : one or more indexes could not be cleaned up. Some indexes may have been cleaned up succesfully.

--Exceptions--:

ORA-24120 covers the following:

a negative (or NULL) index object_id was specified

a negative (or NULL) wait_for_lock was specified the given object_id was not an index object that needed cleanup

--Example Usage of online_index_clean--:

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;

EXCEPTION

WHEN OTHERS THEN

RAISE;

END;

/

NOTE: This operation is a maintenance operation and it will take a large down window if the indexes are large.

Get Oracle Certifications for all Exams
Free Online Exams.com

Index Online Rebuild Fails with ORA-08104: this index object <#> is being online built or rebuilt".

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Symptoms

Index online rebuild fails with error "ORA-08104: this index object 556463 is being online built or rebuilt".

Cause

First online rebuild attempt had failed.  Until SMON performs the clean up, subsequent rebuilds against the index will fail with ORA-8104 or ORA-8106.

Solution

DBMS_REPAIR.ONLINE_INDEX_CLEAN() is only available starting in release 10.2

For 10.1, you need to wait for SMON process to finish the cleanup job.

Note: SMON will perform the cleanup and does this once every 60 minutes. SMON cleanup is only successful if there are no transactions against the base table [or [sub]partition] at the time of the attempted cleanup.  In an environment where there are likely to be uncommitted transactions, this makes cleanup a bit 'hit and miss'.  To speed up the process, you can stop your application which uses the table and wait until the cleanup is done.

Get Oracle Certifications for all Exams
Free Online Exams.com