Restoring data deleted from Oracle database

Information source

• Advanced Oracle Tutorial by Marcin Blaszczyk (slides 45 - 63)

Introduction

For some time after data deletion it is possible to restore it. Notice that it is not ensured that any data will be available to restore after deletion. The possibility of the data being available to restore mainly depends on two factors: the amount of the deleted data and elapsed time.

Viewing past state of table

In order to view the past state of DB table one should run the following SQL command:

SELECT * FROM <tablename>
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '<minutes>' MINUTE);

or

SELECT * FROM <tablename>
AS OF TIMESTAMP TO_DATE('21-AUG-13 11:20:58','DD-MON-YY HH24:MI:SS');

The second command allows you to specify exact time of the DB state that interests you.

Example

To display how table "RunInformation" looked like 60 minutes ago you should run:

SELECT * FROM RunInformation
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' MINUTE);

Restoring data

Restoring dropped table

To try to restore a dropped table tablename run:

FLASHBACK TABLE <tablename> TO BEFORE DROP;

Restoring past state of a table

To restore table tablename to the state that was number minutes ago you should run:

FLASHBACK TABLE <tablename> TO TIMESTAMP SYSTIMESTAMP-INTERVAL '<number>' MINUTE;

Manual restoration of a specific row using sqldeveloper

If you use some graphical software to access DB (e.g. sqldeveloper) and you want to restore only specific rows that were deleted you may run command that displays table's past state (described above), then select relevant rows and find option like "Export..". It will generate a set of commands that you should run to insert those rows into the database.

-- MichalMarciniec - 13-Aug-2013