Forum Stats

  • 3,770,126 Users
  • 2,253,070 Discussions
  • 7,875,331 Comments

Discussions

How to recover dropped index?

kalyan vedagiri
kalyan vedagiri Member Posts: 30 Red Ribbon

I have droped an index but now want to recover it. I have tried

show recyclebin

But there is no index in it that i have dropped.


Could anyone suggest me how to recover dropped index ?

Tagged:

Answers

  • Paulzip
    Paulzip Member Posts: 8,494 Blue Diamond
    edited Jul 16, 2021 8:18AM

    Dropped indexes end up with names like BIN$/yO2LoFDTmObx1GIQtwxOA==$0 in the recycle bin, so you need to ascertain which one is the corresponding one.

    You can flashback the table to before the drop and lookup the "BIN$" index name, then restore it and rename it.

    Here's an article with more detail.

  • kalyan vedagiri
    kalyan vedagiri Member Posts: 30 Red Ribbon

    I have dropped only index, I haven't dropped table . And also in recyclebin I could not find the index that I have deleted (in original_name column ).

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,790 Gold Crown


    You can't "recover" an index on its own, you can only recreate it with a "create index" statement. If that's not possible and you really have to do a recovery for some reason then you'd have to recover a self-consistent set of tablespaces that included the index and the table it was on up to a point in time before the drop - and that's probably not an acceptable strategy since it may mean you've got a load of data to recreate which may introduced some other inconsistencies.

    If you're worried about being able to find the definition of the index you can query the data dictionary as at a point of time in the past, e.g. I have a table CHILD where I dropped and index a few seconds ago; and I have a note of the current_scn (from v$database).SQL> select table_name, index_name, column_name, column_position from dba_ind_columns where table_owner = 'TEST_USER' and table_name = 'CHILD';

    no rows selected
    
    
    SQL> select table_name, index_name, column_name, column_position from dba_ind_columns as of scn 4640450 where table_owner = 'TEST_USER' and table_name = 'CHILD' order by column_position;
    
    TABLE_NAME           INDEX_NAME           COLUMN_NAME          COLUMN_POSITION
    -------------------- -------------------- -------------------- ---------------
    CHILD                CHI_PK               ID_P                               1
    CHILD                CHI_PK               ID                                 2
    
    2 rows selected.
    
    
    SQL> select table_name, index_name, column_name, column_position
      2  from dba_ind_columns  as of timestamp to_timestamp('16-Jul-2021 07:50','dd-mon-yyyy hh24:mi')
      3  where table_owner = 'TEST_USER'
      4  and table_name = 'CHILD'
      5* order by column_position
    SQL> /
    
    TABLE_NAME           INDEX_NAME           COLUMN_NAME          COLUMN_POSITION
    -------------------- -------------------- -------------------- ---------------
    CHILD                CHI_PK               ID_P                               1
    CHILD                CHI_PK               ID                                 2
    


    Regards

    Jonathan Lewis