Flashback Table to Recover Deleted Records in Oracle Database

When Oracle database is configured with flashback area, it is possible to recover lost data from flashback.  Depending on the size of flashback area, records that were removed from database may still stay in the flashback for certain period of time.  For example, in a data cleansing project, a staff accidentally deleted 4000 records in ACCOUNTING table.  Although these records were gone after the commit, they may be seen in the flashback area.  The following query is used to check the previous contents of a table in a given point of time:


SELECT * FROM ACCOUNTING AS OF TIMESTAMP TO_TIMESTAMP ('20130501 13:50:00', 'YYYYMMDD HH24:MI:SS');

In this case the accidental removal of records occurred at 2pm, thus if we check the flashback area at the point of time 10 minutes before the occurrence, we can see all of the deleted data.

Because the flashback area can only hold certain amount of data, we cannot flash way back of the time, for instance, if we try to check data state as of a month ago, we get error message:

SELECT * FROM ACCOUNTING AS OF TIMESTAMP TO_TIMESTAMP ('20130401 13:50:00', 'YYYYMMDD HH24:MI:SS');
ORA-08180: no snapshot found based on specified time

Thus flashback recovery is very time-sensitive.

Once we verified that the deleted data are still in the flashback area, we need to retrieve the lost data as soon as possible.  One way is to quickly backup them to a new table.  The following query retrieves exact records that were deleted:

SELECT * FROM ACCOUNTING AS OF TIMESTAMP TO_TIMESTAMP ('20130501 13:50:00', 'YYYYMMDD HH24:MI:SS') WHERE acc_id is not in (select acc_id from ACCOUNTING);

Here acc_id is the primary key of the ACCOUNTING table.

Now let's backup the lost records to a new table:

create table ACCOUNTING_DEL_20130501 as SELECT * FROM ACCOUNTING AS OF TIMESTAMP TO_TIMESTAMP ('20130501 13:50:00', 'YYYYMMDD HH24:MI:SS') WHERE acc_id is not in (select acc_id from ACCOUNTING);

Next we recover the table to the time point prior to the deletion.

First, we need to enable row movement on this table:

alter table ACCOUNTING enable row movement;

Next we flashback the table:

flashback table siebel.cx_doc_auth_sub to timestamp to_timestamp('20130501 13:50:00', 'YYYYMMDD HH24:MI:SS');

After check the table to confirm data recovered, we need to disable row movement:

alter table ACCOUNTING disable row movement;

This is just a quick approach to bring the table back to a antecedent state.  However, if there are other transactions, this approach is not applicable.  Therefore another common practice is to manually insert the retrieved record back to the table.