how to recover dropped table in oracle?

how to recover dropped table in oracle?

Example:-

SQL>create table test_backup(id number);


SQL>  begin
  2   for i in 1..10 loop
  3  insert into test_backup values(i);
  4  end loop;
  5   end;
  6   /


PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL>  begin
  2   for i in 1..10000 loop
  3   insert into test_backup values(i);
  4   end loop;
  5  end;
  6   /


PL/SQL procedure successfully completed.


SQL> commit;


Commit complete.

SQL> create index test_backup_ndx on test_backup(id);

Index created.

SQL> select index_name,STATUS from user_indexes where lower(table_name)='test_backup';


INDEX_NAME
------------------------------
TEST_BACKUP_NDX


SQL> select count(*) from test_backup;

  COUNT(*)
----------
     10010

SQL>  SHOW RECYCLEBIN

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST             BIN$3pJCivMX4efgQKjAh4A0sg==$0 TABLE        2013-06-07:20:00:34
TEST             BIN$3pJCivMW4efgQKjAh4A0sg==$0 TABLE        2013-06-07:19:59:34

note:- test table name not present in recyclebin list.

SQL> drop table test_backup;

Table dropped.


SQL>  SHOW RECYCLEBIN

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST             BIN$3pJCivMX4efgQKjAh4A0sg==$0 TABLE        2013-06-07:20:00:34
TEST             BIN$3pJCivMW4efgQKjAh4A0sg==$0 TABLE        2013-06-07:19:59:34
TEST_BACKUP      BIN$3swJiRz/0tbgQKjAh4Bysw==$0 TABLE        2013-06-10:17:05:09

note:- now test_backup table name present in RECYCLEBIN list.

SQL>  select count(*) from "BIN$3swJiRz/0tbgQKjAh4Bysw==$0";


     COUNT(*)
      ----------
      10010


SQL> flashback table test_backup to before drop;


Flashback complete.


SQL>  SHOW RECYCLEBIN

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST             BIN$3pJCivMX4efgQKjAh4A0sg==$0 TABLE        2013-06-07:20:00:34
TEST             BIN$3pJCivMW4efgQKjAh4A0sg==$0 TABLE        2013-06-07:19:59:34

note:- now test table name not present in recyclebin list.

sql> select count(*) from test_backup;

  COUNT(*)
----------
     10010

SQL> select index_name,STATUS from user_indexes where lower(table_name)='test_backup';

INDEX_NAME                     STATUS
------------------------------ --------
BIN$3swJiRz+0tbgQKjAh4Bysw==$0 VALID


SQL>alter index "BIN$3swJiRz+0tbgQKjAh4Bysw==$0" rename to TEST_BACKUP_NDX;


SQL> select index_name,STATUS from user_indexes where lower(table_name)='test_backup';

INDEX_NAME                     STATUS
------------------------------ --------
TEST_BACKUP_NDX                VALID