Tuesday, April 5, 2011

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



4 comments:

Anonymous said...

Brokersring.com - Learn how to turn $500 into $5,000 in a month!

[url=http://www.brokersring.com/]Make Money Online[/url] - The Secret Reveled with Binary Option

Binary Options is the way to [url=http://www.brokersring.com/]make money[/url] securely online

Blogger said...

Searching for the Best Dating Site? Create an account to find your perfect date.

raybanoutlet001 said...

nike shoes
reebok shoes
cheap oakley sunglasses
oklahoma city thunder jerseys
coach outlet online
ugg outlet
nike blazer pas cher
ugg boots
coach outlet
gucci shoes

jeje said...

kyrie irving shoes
jordan 13
adidas outlet
air jordan 11
adidas nmd
jordan retro
longchamp handbags
lacoste online shop
birkin bag
adidas eqt support adv