Restoring data deleted from Oracle database

Information source

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

Topic attachments
I Attachment History Action Size Date Who Comment
PDFpdf 20130508_AdvancedSQL_Oracle_Tutorials_May2013.pdf r1 manage 2235.1 K 2013-08-13 - 15:47 MichalMarciniec Advanced Oracle Tutorial by Marcin Blaszczyk
Edit | Attach | Watch | Print version | History: r6 < r5 < r4 < r3 < r2 | Backlinks | Raw View | WYSIWYG | More topic actions
Topic revision: r6 - 2013-08-19 - MichalMarciniec
 
    • Cern Search Icon Cern Search
    • TWiki Search Icon TWiki Search
    • Google Search Icon Google Search

    TOTEM All webs login

This site is powered by the TWiki collaboration platform Powered by PerlCopyright & 2008-2020 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
or Ideas, requests, problems regarding TWiki? use Discourse or Send feedback