Search This Blog

Wednesday, May 14, 2008

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.

3 comments:

Anonymous said...

Thanks, it is very useful.

Another question.

Recycle Bin is a method for recover dropped table.

Is there another method to recover DROPPED TABLE?

Anonymous said...

Is this really supposed to work? The first examle works but dropping a table is an DDL command. Using only Flashback Query is - as you write - not supposed to work on DDL. Flashback drop needs the recyclebin, which requires Flashback to be set to ON. Flashback guery only needs the Undo data.

Anonymous said...

Sorry! Scott deleted the table. He did not drop it. Delete is DML. This works! It would be nice if my reading capabilities were better.