Recover a Dropped Table Using Oracle Flashback Drop

Before You Begin

This 10-minute tutorial shows you how to use Oracle Flashback Drop to recover a dropped table.

Select the Oracle Database release:

Background

Oracle Flashback Drop enables you to reverse the effects of dropping (deleting) a table, returning the dropped table to the database along with dependent objects such as indexes and triggers. This feature stores dropped objects in a recycle bin, from which they can be retrieved until the recycle bin is purged, either explicitly or because space is needed.

What Do You Need?

Oracle Database 18c19c

Create a New Table

In this section you create a new table.

Invoke SQL*Plus and connect as the SYSTEM user.

Alter session to pluggable database container orclpdb.

# SQL>  alter session set container=orclpdb;

Session altered.

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.
```

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.

Execute the DROP TABLE command to drop the table.
```
SQL>drop table hr.regions_hist;

Table dropped.
```

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.

Execute the FLASHBACK TABLE command to recover the dropped table.
```
SQL>flashback table hr.regions_hist to before drop;

Flashback complete.
```

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

Exit from SQL*Plus.