Flashback Query (SELECT ... AS OF)

You perform a Flashback Query using a SELECT statement with an AS OF clause. You use a Flashback Query to retrieve data as it existed at some time in the past. The query explicitly references a past time using a timestamp or SCN. It returns committed data that was current at that point in time.


Uses of Flashback Query include:

Recovering lost data or undoing incorrect, committed changes. For example, if you mistakenly delete or update rows, and then commit them, you can immediately repair the mistake.

Comparing current data with the corresponding data at some time in the past. For example, you might run a daily report that shows the change in data from yesterday. You can compare individual rows of table data, or find intersections or unions of sets of rows.

Here is the show for you...

C:\>sqlplus flash/flash


SQL*Plus: Release 10.2.0.1.0 - Production on Tue Apr 5 11:11:31 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create table fq (id number);

Table created.

SQL> insert into fq values(1);

1 row created.

SQL> commit;

Commit complete.


SQL> select * from fq as of timestamp TO_TIMESTAMP('2011-04-05 11:13:00', 'YYYY-
MM-DD HH:MI:SS')
  2  ;

        ID
----------
         1
-------------------Quering data through flashback query----------------

SQL> select * from fq as of timestamp TO_TIMESTAMP('2011-04-05 11:12:12', 'YYYY-
MM-DD HH:MI:SS')
  2  ;

no rows selected

-------------------Quering data through flashback query----------------

SQL> select * from fq as of timestamp TO_TIMESTAMP('2011-04-05 11:12:59', 'YYYY-
MM-DD HH:MI:SS')
  2  ;

        ID
----------
         1

Now.........

SQL> update fq set id=78;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from fq;

        ID
----------
        78

-------------------Updating new values with previous value------------------

SQL> update fq set id= (select * from fq as of timestamp TO_TIMESTAMP('2011-04-0
5 11:12:59', 'YYYY-MM-DD HH:MI:SS'));

1 row updated.

SQL> commit;

Commit complete.


----------Now u got right-----------------

SQL> select * from fq;

        ID
----------
         1

:-)Enjoy