Home > administration > online rebuild index fails ora-8104

online rebuild index fails ora-8104

When you kill a session which is rebuilding an index this will cause an ora-8104.
SQL> ALTER INDEX I_TEST  REBUILD ONLINE ;
ALTER INDEX I_TEST  REBUILD ONLINE
*
ERROR at line 1:
ORA-08104: this index object 11040 is being online built or rebuilt

Solution:

When starting index rebuild online, the new indes wil be created parallel to the current one.

During the kill (immediate) some extents still exists on the tablespace and the index has a marker a online operation is initiated.

To cleanup the dirt use the procedure DBMS_REPAIR.ONLINE_INDEX_CLEAN

DECLARE
RetVal BOOLEAN;
 OBJECT_ID BINARY_INTEGER;
 WAIT_FOR_LOCK BINARY_INTEGER;
BEGIN
   OBJECT_ID := 11040;
   WAIT_FOR_LOCK := NULL;
   RetVal := SYS.DBMS_REPAIR.ONLINE_INDEX_CLEAN (OBJECT_ID);
   COMMIT;
 END;
 /

Now you can start again your rebuild operation.

Advertisements
Categories: administration Tags: ,
  1. No comments yet.
  1. No trackbacks yet.

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 )

Connecting to %s

%d bloggers like this: