Restore Data With Flashback Query

Flashback Query allows a user to view the data quickly and easily the way it was at a particular time in the past, even when it is modified and committed for a single row or the whole table.

This feature is used in both 9i and 10g database versions with no need to be enabled or disbaled.

SQL> CREATE TABLE TEST AS SELECT TABLE_NAME FROM USER_TABLES;

SQL> SELECT * FROM TEST;

TABLE_NAME
---------------
BONUS
CLAS_ROLLS
CLAS_TRUTH
COMP
DEPT
EMP

SQL> ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY HH24:MI:SS.FF3';

SQL> SELECT LOCALTIMESTAMP FROM DUAL;

LOCALTIMESTAMP
------------------------
25-DEC-2009 21:31:01.750

SQL> UPDATE TEST SET TABLE_NAME= 'TEST';

SQL> COMMIT;

SQL> SELECT * FROM TEST;

TABLE_NAME
---------------
TEST
TEST
TEST
TEST
TEST
TEST

Now we can invoke flashback query as of the timestamp prior to our update.

SQL> SELECT *
FROM TEST AS OF TIMESTAMP TO_TIMESTAMP('25-DEC-2009 21:31:01.750');

TABLE_NAME
---------------
BONUS
CLAS_ROLLS
CLAS_TRUTH
COMP
DEPT
EMP

Now you have the old data you can use it to recover the data updated/deleted by mistake.