Saturday, October 1, 2011

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

0 comments: