Recovering from a Dropped Table: Scenario

One not-uncommon error is the accidental dropping of a table from your database. In general, the fastest and simplest solution is to use the flashback drop feature, described inOracle Database Backup and Recovery Basics, to reverse the dropping of the table. However, if for some reason, such as flashback drop being disabled or the table having been dropped with the PURGE option, you cannot use flashback table, you can create a copy of the database, perform point-in-time recovery of that copy to a time before the table was dropped, export the dropped table using an Oracle export utility, and re-import it into your primary database using an Oralce import utility.

In this scenario, assume that you do not have the flashback database functionality enabled, so FLASHBACK DATABASE is not an option, but you do have physical backups of the database.

Note:

Grant powerful privileges (such as DROP ANY TABLE) only to only selected, appropriate users, to minimize user errors that require database recovery.

To recover a table that has been accidentally dropped, use the following procedure:

If possible, keep the database that experienced the user error online and available for use. Back up all datafiles of the existing database in case an error is made during the remaining steps of this procedure.
Restore a database backup to an alternnate location, then perform incomplete recovery of this backup using a restored backup control file, to the point just before the table was dropped.
Export the lost data from the temporary, restored version of the database using an Oracle export utility. In this case, export the accidentally dropped table.

Note:
System audit options are exported.
Use an Oracle import utility to import the data back into the production database.
Delete the files of the temporary copy of the database to conserve space.