Recover a Dropped Table Using Oracle Flashback Drop

 

Create a New Table

In this section you create a new table.

1. Invoke SQL*Plus and connect as the SYSTEM user.
2. Alter session to pluggable database container orclpdb.

   # SQL>  alter session set container=orclpdb;
   
   Session altered.

3. Create a new table named HR.REGIONS_HIST. Use the following statement to create a table that has the same structure and content as the HR.REGIONS table.

   SQL>create table hr.regions_hist as select * from hr.regions; 
   
   Table created.

4. Query the new HR.REGIONS_HIST table.

   SQL>select * from hr.regions_hist;
   
    REGION_ID REGION_NAME
   ---------- -------------------------
            1 Europe
            2 Americas
            3 Asia
   4 Middle East and Africa

---

Drop a Table

In this section you drop your new HR.REGIONS_HIST table.

1. Execute the DROP TABLE command to drop the table.

   SQL>drop table hr.regions_hist;
   
   Table dropped.

2. Query the HR.REGIONS_HIST table.

   SQL>select * from hr.regions_hist;
   select * from hr.regions_hist
                    *
   ERROR at line 1:
   ORA-00942: table or view does not exist

   Notice that you get an error because the table is dropped.

---

Recover the Dropped Table

In this section you recover the HR.REGIONS_HIST table from the recycle bin.

1. Execute the FLASHBACK TABLE command to recover the dropped table.

   SQL>flashback table hr.regions_hist to before drop;
   
   Flashback complete.

2. Once again query the HR.REGIONS_HIST table to verify the data has been restored.

   SQL>select * from hr.regions_hist;
   
    REGION_ID REGION_NAME
   ---------- -------------------------
            1 Europe
            2 Americas
            3 Asia
   4 Middle East and Africa

3. Exit from SQL*Plus.