ARCHIVED: In Oracle 10g and later, how do I recover a dropped database table?

The recyclebin feature introduced in Oracle 10g allows you to recover dropped tables using the flashback table...to before drop command. With recyclebin, Oracle does not automatically delete dropped tables. Instead, Oracle renames dropped and their associated objects, giving them system-generated recyclebin names that begin with BIN$.

For the following examples, consider creating and then dropping this simple table (testing):

• Create testing:

    SQL> create table testing (col varchar2(10), row_chng_dt date);
  
    Table created.
  
    SQL> insert into testing values ('Version1', sysdate);
  
    1 row created.
  
    SQL> select * from testing ;
  
    COL        ROW_CHNG
    ---------- --------
    Version1   16:10:03

• Drop testing:

    SQL> drop table testing; 
  
    Table dropped

Dropping testing places it in recyclebin and changes its name to a recyclebin object name:

• To find the new name, query recyclebin:

    SQL> select object_name, original_name, type from recyclebin; 
    
    OBJECT_NAME                       ORIGINAL_NAME              TYPE      
    ------------------------------    -----------------------    -------      
    BIN$HGnc55/BrRPgQPeM/qQoRw==$0    TESTING_JOB_IX             INDEX
    BIN$HGnc55/CrRPgQPeM/qQoRw==$0    TESTING_EMPLOYEE_IX        INDEX
    BIN$HGnc55/DrRPgQPeM/qQoRw==$0    TESTING_DEPARTMENT_IX      INDEX
    BIN$HGnc55/ErRPgQPeM/qQoRw==$0    TESTING_EMP_ID_ST_DATE_PK  INDEX
    BIN$HGnc55/ArRPgQPeM/qQoRw==$0    TESTING                    TABLE

• Although the dropped table is renamed, it retains its data, and you can query it as you would a normal table:

    SQL> select * from "BIN$HGnc55/ArRPgQPeM/qQoRw==$0" ;
  
    COL        ROW_CHNG
    ---------- --------
    Version1   16:10:03

Because a dropped table retains its data, you can easily "undrop" the table by using the flashback table... to before drop command to revert the dropped table to its original name.

Note: Although this command changes the table name, it does not rename any of the dependent objects (e.g., indexes) associated with the table. To revert the dependent objects to their original names, you must manually rename each of them. If you plan to do this, make sure you note the system-generated recyclebin names for each of the dependent objects before you "undrop" the table.

After noting the recyclebin names of any dependent objects in the dropped table, use the following command to restore the table (e.g., change its name back to testing):

  SQL> flashback table testing to before drop;

  Flashback complete.

Associated indexes are restored with the table, but they retain their recyclebin names; for example:

  SQL> select index_name from user_indexes where table_name = 'testing';

  BIN$HGnc55/BrRPgQPeM/qQoRw==$0
  BIN$HGnc55/CrRPgQPeM/qQoRw==$0
  BIN$HGnc55/DrRPgQPeM/qQoRw==$0
  BIN$HGnc55/ErRPgQPeM/qQoRw==$0

To revert the indexes to their original names, you must use the following command for each index:

  alter index "recyclebin_name" rename to original_name;

For example:

  alter index "BIN$HGnc55/BrRPgQPeM/qQoRw==$0" rename to TESTING_JOB_IX;
  alter index "BIN$HGnc55/CrRPgQPeM/qQoRw==$0" rename to TESTING_EMPLOYEE_IX;
  alter index "BIN$HGnc55/DrRPgQPeM/qQoRw==$0" rename to TESTING_DEPARTMENT_IX;
  alter index "BIN$HGnc55/ErRPgQPeM/qQoRw==$0" rename to TESTING_EMP_ID_ST_DATE_PK;


  SQL> select * from testing ; 
  COL        ROW_CHNG 
  ----------   -------- 
  Version1 16:10:03 

  SQL> select * from recyclebin ; 
  no rows selected

When a table is dropped, it is only renamed, not deleted. It remains part of your tablespace and counts against your user tablespace quota. To reclaim tablespace, use flashback to restore tables, or use purge to clear them from recyclebin; for example:

  SQL> purge table "BIN$HGnc55/ArRPgQPeM/qQoRw==$0" ;

  Table purged.

Otherwise, objects will remain in recyclebin until either the tablespace is exhausted or your user quota on the tablespace is met. In either case, Oracle will begin purging objects one at a time, starting with those that have been kept in recyclebin the longest, until it creates enough space for the current operation.

For additional information, see Using Flashback Drop and Managing the Recycle Bin in the Oracle Database Administrator's Guide.