Oracle Flashback Query

Oracle Flashback Query
We can recover lost records which done by DML statement not DDL.

Through this feature we can able to perform below task
1. Recovering lost data or undoing incorrect, commit changes.
2. Comparing current data with the corresponding data at some time in the past
3. Checking the state of transactional data at a particular time
4. Simplifying application design by removing the need to store some kinds of temporal data

Example:
User Scott delete empcopy table by mistake at 7:53 pm and also delete one record from emp table at 7:25 pm and commit.
After he found his doing incorrect operation on empcopy or emp table now he want to recover lost records without performing incomplete recovery or export/import method.

Through Flashback query we can recover lost transaction, we can query past data with SELECT … AS OF TIMESTAMP or SCN (system change number)

SQL> select empno,sal,ename from emp
2 AS OF TIMESTAMP to_timestamp('2008-05-14 19:24:00','YYYY-MM-DD HH24:MI:SS')
3 where empno = 7934;
EMPNO SAL ENAME
---------- ---------- ----------
7934 1300 MILLER

Insert this record again in EMP table
SQL> insert into emp
2 ( select * from emp
3 AS OF TIMESTAMP to_timestamp('2008-05-14 19:24:00','YYYY-MM-DD HH24:MI:SS')
4 where empno = 7934);
1 row created.
SQL> commit;
Commit complete.

Now recover empcopy table

SQL> insert into empcopy
2 ( select * from empcopy
3 AS OF TIMESTAMP to_timestamp('2008-05-14 19:54:00','YYYY-MM-DD HH24:MI:SS')
4 );
13 rows created.
SQL> commit;
Commit complete.

NOTE: Undo_retention parameter must set higher for keep undo records.