Andrew Fraser DBA

Flashback query alternative to point in time recovery

If data has been removed due to user error, it can be easily and quickly be brought back without the need for restores, using flashback query:

CREATE TABLE temp_rescue AS SELECT * FROM owner.table AS OF TIMESTAMP TO_TIMESTAMP ( 'Thu 14-Nov-2013 13:00:00' , 'Dy DD-Mon-YYYY HH24:MI:SS' )  ;

Or if the entire table has been dropped, rather than just some of its data:

FLASHBACK TABLE owner.table TO BEFORE DROP ;

Note that this is for flashback query, which is (confusingly) unrelated to flashback database, flashback area, or db_flashback_retention_target. Instead flashback query is related to undo_retention. Flashback query is always on and available, but the unrelated flashback database is switched off by default (see with ‘select flashback_on from v$database’).

By default undo_retention is set to 900s = 15 minutes: not very long, but that is a target rather than a definite value, in practice can often flashback query to much longer back. If you do want longer flashback query times, you can set up a flashback data archive, but that is not in place by default, has to be configured by a DBA beforehand.

You can use flashback query with data dictionary objects like all_source, useful if pl/sql code has been changed by mistake.