Recover a Dropped Table Using Oracle Flashback Drop (2024)

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.

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 Asia4 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 Asia4 Middle East and Africa

3. Exit from SQL*Plus.