Tales From A Lazy Fat DBA

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

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

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

2. Restore a database backup to an alternate location, then perform incomplete recovery of this backup using a restored backup control file, to the point just before the table was dropped.

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

4. Use an Oracle import utility to import the data back into the production database.

5. Delete the files of the temporary copy of the database to conserve space.