DBMS_REPAIR and Unfinished Online Index Rebuilds

I have, seriously, no clue how I haven’t seen this before today, but in case you haven’t either, this is how to use DBMS_REPAIR to clean up after unfinished online index rebuilds.

I know there are opposing schools of thought with rebuilding or reorganizing indexes, but, for the sake of argument, let’s say that I decided to perform an online rebuild of the MARKTAB_UX_IDX index in the MARKAPP schema this afternoon.

It doesn’t really matter why – Segment Advisor jumping up and down with its hair on fire, a shot-in-the-dark request from an application vendor or a DBA trying to extending a datafile and avoiding adding to the “tab” running with the storage guys – let’s just assume I issue this command:

ALTER INDEX markapp.marktab_uk_idx REBUILD ONLINE TABLESPACE markapp_i01;

I kick it off, but nothing appears to be happening. After a while, I check for blocking locks and notice that a user is running something which is holding an exclusive lock on the MARKAPP.MARKTAB table – something to add to my list of things to investigate – thus preventing me from starting the index rebuild.

After determining that user is doing something important and really can’t be persuaded to release that exclusive lock for the next hour or two, let’s say that I decide to kill my session to stop the ALTER INDEX … statement.

Again, it might not be the best decision, but bear with me.

Being the ever-neurotic DBA, I make sure that I haven’t invalidated the index before I proceed. A quick look in the DBA_INDEXES view shows that the index is VALID.

However, when the user has finished with holding the lock, I re-issue the ALTER INDEX … statement but immediately get this error:

Error starting at line 1 in command:

ALTER INDEX MARKAPP.MARKTAB_UK_IDX REBUILD ONLINE TABLESPACE MARKAPP_I01

Error report:

SQL Error: ORA-08104: this index object 295701 is being online built or rebuilt

08104. 00000 – “this index object %s is being online built or rebuilt”

*Cause: the index is being created or rebuild or waited for recovering

from the online (re)build

*Action: wait the online index build or recovery to complete

Interesting. I didn’t want to try, but I suspect that queries which tried to use that index might have got the same error message. Not ideal considering it was meant to be an “online index rebuild”.

Fret, worry and pine not, though, you don’t have to drop and recreate the index to resolve this error – you have to “repair” it using the DBMS_REPAIR.ONLINE_INDEX_CLEAN function.

The ORA-08104 message will tell you the object_id that you need to pass to the function – in this case, 295701. Armed with that info, you can run the following anonymous block:

DECLARE;

lv_ret BOOLEAN;

BEGIN

lv_ret := DBMS_REPAIR.ONLINE_INDEX_CLEAN(295701);

END;

/

anonymous block completed.

Now try rebuilding the index again:

ALTER INDEX markapp.marktab_uk_idx REBUILD ONLINE TABLESPACE markapp_i01;

index markapp.marktab_uk_idx altered.

And now to figure out why the user was holding an exclusive lock on the MARKAPP.MARKTAB table …

Advertisements
Tagged , , , , ,

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: