ORA-08104: This Index Object X Is Being Online Built …

Today I was rebuilding an index that has about 300 gigabytes (using parallel, nologging, online, etc..) And took a long when I’ve canceled it. After adjusting session-level parameters, I get the following error when executing it again;

PROD*ANR> ALTER INDEX siebel.S_EVT_ACT_P1 rebuild nologging online parallel 3
2 ;
ALTER INDEX siebel.S_EVT_ACT_P1 rebuild nologging online parallel 3
*
ERROR en línea 1:
ORA-08104: this index object 419709 is being online built or rebuilt

This is a known bug and that happens quite often in versions 10, has been a lock to be released we will use one of twice  scripts below;

From Oracle documentation

DBMS_REPAIR.ONLINE_INDEX_CLEAN (
object_id IN BINARY_INTEGER DEFAULT ALL_INDEX_ID,
wait_for_lock IN BINARY_INTEGER DEFAULT LOCK_WAIT)
RETURN BOOLEAN;

One time, it can doesn’t work. In our case only one index (object_id = 419709)

SET SERVEROUTPUT ON SIZE UNLIMITED

DECLARE
OK BOOLEAN;
BEGIN
OK := FALSE;
OK := DBMS_REPAIR.ONLINE_INDEX_CLEAN (419709, DBMS_REPAIR.LOCK_NOWAIT);

IF OK THEN
DBMS_OUTPUT.PUT_LINE (‘ RESULT : OK ‘);
ELSE
DBMS_OUTPUT.PUT_LINE (‘ RESULT : KO ‘);
END IF;

END;
/

With pooling, until free the lock

DECLARE
OK BOOLEAN;
BEGIN
OK := FALSE ;
WHILE (OK = FALSE) loop
OK := DBMS_REPAIR.ONLINE_INDEX_CLEAN (419709, DBMS_REPAIR.LOCK_NOWAIT);
DBMS_LOCK.SLEEP (60); — Sleep for one minute
END LOOP;
END;
/

HTH – Antonio NAVARRO

Advertisements

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