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