Online index rebuild cleanup

 If session performing online index rebuild will be killed by mistake Oracle end up with some inconsistency inside dictionary. If you try to re-run killed command to create index once more database will complain that index already exist. Although if you try to drop index database will complain that index is in rebuild state and could not be dropped at that time.
To fix that issue you need to use package dbms_repair as showed in example:

1. Building test table
   

   SQL> create table test tablespace users as select rownum id, 'xxxxxxxxxxxxxxxxxxxxxxxx' col1 from dba_source, dba_source where rownum < 10000000;
   Table created.
   

2. New index build has been started with online clause and session has been killed
   

   SQL> create index test_index on test (id) tablespace users online;
   create index test_index on test (id) tablespace users online
                                                              *
   ERROR at line 1:
   ORA-03113: end-of-file on communication channel
   Process ID: 4356
   Session ID: 191 Serial number: 13
   

3. Sanity check - index exist even if build has been never completed.
   

   SQL> select index_name from dba_indexes where table_name = 'TEST';
   INDEX_NAME
   ------------------------------
   TEST_INDEX
   

4. Now let's try to drop it
   

   SQL> drop index test_index;
   drop index test_index
              *
   ERROR at line 1:
   ORA-08104: this index object 66960 is being online built or rebuilt
   

5. So maybe we can create it again ?
   

   SQL> create index test_index on test (id) tablespace users online;
   create index test_index on test (id) tablespace users online
                *
   ERROR at line 1:
   ORA-00955: name is already used by an existing object
   

6. Let's clean it up and allow Oracle to drop index - ONLINE_INDEX_CLEAN need a object number which can be taken from drop index error message or using the following query (at least it worked for me).
   

   SQL> select min(object_id) from dba_objects where object_name = 'TEST_INDEX';
   
   MIN(OBJECT_ID)
   --------------
           66960
   
   SQL> declare
     2  ret boolean;
     3
     4  begin
     5      ret:=dbms_repair.ONLINE_INDEX_CLEAN(66960);
     6  end;
     7  /
   
   PL/SQL procedure successfully completed.
   

   and now let's check if index is still there
   

   SQL> select index_name from dba_indexes where table_name = 'TEST';
   no rows selected
   

Hope it help to solve some of your problems.

regards,

Marcin