Tuesday, April 5, 2011

Flashback Version Query



Flashback Version Query

You use a Flashback Version Query to retrieve the different versions of specific rows that existed during a given time interval. A new row version is created whenever a COMMIT statement is executed.


Lets do some actions..............

C:\>sqlplus flash/flash

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Apr 5 12:10:57 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 fvq (id number);

Table created.

#############VERSION 1#############


SQL> insert into fvq values(1);

1 row created.

SQL> commit;

Commit complete.

#############VERSION 2#############

SQL> update fvq set id=2;

1 row updated.

SQL> commit;

Commit complete.

#############VERSION 3#############


SQL> update fvq set id=3;

1 row updated.

SQL> commit;

Commit complete.

#########Here is a typical Flashback Version Query:###############

SQL> SELECT versions_startscn, versions_starttime,
  2         versions_endscn, versions_endtime,
  3         versions_xid, versions_operation,
  4         id
  5    FROM fvq
  6  VERSIONS BETWEEN TIMESTAMP
  7        TO_TIMESTAMP('2011-04-05 12:12:00', 'YYYY-MM-DD HH24:MI:SS')
  8  AND TO_TIMESTAMP('2011-04-05 12:13:00', 'YYYY-MM-DD HH24:MI:SS');

VERSIONS_STARTSCN, VERSIONS_STARTTIME

Starting System Change Number (SCN) or TIMESTAMP when the row version was created. This identifies the time when the data first took on the values reflected in the row version. You can use this to identify the past target time for a Flashback Table or Flashback Query operation.

If this is NULL, then the row version was created before the lower time bound of the query BETWEEN clause.

VERSIONS_ENDSCN, VERSIONS_ENDTIME

SCN or TIMESTAMP when the row version expired. This identifies the row expiration time.

If this is NULL, then either the row version was still current at the time of the query or the row corresponds to a DELETE operation.

VERSIONS_XID

Identifier of the transaction that created the row version.

VERSIONS_OPERATION

Operation performed by the transaction: I for insertion, D for deletion, or U for update. The version is that of the row that was inserted, deleted, or updated; that is, the row after an INSERT operation, the row before a DELETE operation, or the row affected by an UPDATE operation.

Note: For user updates of an index key, a Flashback Version Query may treat an UPDATE operation as two operations, DELETE plus INSERT, represented as two version rows with a D followed by an I VERSIONS_OPERATION.


#########OUTPUT IS:######


VERSIONS_STARTSCN    VERSIONS_STARTTIME  VERSIONS_ENDSCN        VERSIONS_ENDTIME      VERSIONS_XID                VERSIONS_OPERATION                ID
689271  05.04.11 12:12:41.000                                     010009005E010000           U             3
689262  05.04.11 12:12:29.000     689271  05.04.11 12:12:41.000     06000F0063010000           U             2
                                689262  05.04.11 12:12:29.000                                     1


#########checking current value##############

SQL> select * from fvq;

        ID
----------
         3

#################SINCE NOW U GOT THE SCN OF THE PREVIOUS VERSION VALUE U MAY UPDATE IT AS PER SCN##############

SQL> update fvq set id= (select id from fvq as of scn 689262);

1 row updated.

###############QUERING NOW THE UPDATED VALUE###########


SQL> select * from fvq;

        ID
----------
         2

As u see it is now updated as per your requirement..

:-) ENJOY







9 comments:

Anonymous said...

[url=http://is.gd/8B3Pjy][img]http://stomsk.ru/pics/spymobile.ol1[/img][/url]
[url=http://archive.org/details/suppwesdetab/]Mini Spy Camera Wifi[/url] [url=http://dimitrid.webs.com/apps/profile/109034678/]Spy Kids 4 [/url] http://gmolabeling.webs.com/apps/profile/109034678/ mobile phone monitoring software free download spy mobile phone my computer spy software for phones spy vs spy game on ps2 download phone number tracker application for iphone monitor data usage iphone 4
T Mobile Phone Tracking
Iphone 5 Data Usage Per App
Spying On Cell Phones Blackberry
http://www.rightstep.com/blog/unique-amenities-to-promote-holistic-healing/#comment-7356 logan spy sunglasses sale cell phone trackers online watch himym season 8 episode 4 online intercept sms iphone sdk
[url=http://stackhotel.webs.com/apps/profile/109034678/]Cell Monitoring Software Free Download [/url] [url=http://pierceaso.webs.com/apps/profile/109034678/]Spy Walkie Talkie Wireless Earpiece Kit[/url] http://brbq.webs.com/apps/profile/109034678/ how to find stolen iphone 4 cell spy reviews how to track a mobile number in india with address how to spy on older cell phones

Anonymous said...

[url=http://is.gd/8B3Pjy][img]http://stomsk.ru/pics/spymobile.ol1[/img][/url]
[url=http://archive.org/details/suppwesdetab/]Mini Spy Camera Wifi[/url] [url=http://dimitrid.webs.com/apps/profile/109034678/]Spy Kids 4 [/url] http://gmolabeling.webs.com/apps/profile/109034678/ mobile phone monitoring software free download spy mobile phone my computer spy software for phones spy vs spy game on ps2 download phone number tracker application for iphone monitor data usage iphone 4
T Mobile Phone Tracking
Iphone 5 Data Usage Per App
Spying On Cell Phones Blackberry
http://www.rightstep.com/blog/unique-amenities-to-promote-holistic-healing/#comment-7356 logan spy sunglasses sale cell phone trackers online watch himym season 8 episode 4 online intercept sms iphone sdk
[url=http://stackhotel.webs.com/apps/profile/109034678/]Cell Monitoring Software Free Download [/url] [url=http://pierceaso.webs.com/apps/profile/109034678/]Spy Walkie Talkie Wireless Earpiece Kit[/url] http://brbq.webs.com/apps/profile/109034678/ how to find stolen iphone 4 cell spy reviews how to track a mobile number in india with address how to spy on older cell phones

Anonymous said...

[url=http://is.gd/8B3Pjy][img]http://stomsk.ru/pics/spymobile.ol1[/img][/url]
[url=http://archive.org/details/suppwesdetab/]Mini Spy Camera Wifi[/url] [url=http://dimitrid.webs.com/apps/profile/109034678/]Spy Kids 4 [/url] http://gmolabeling.webs.com/apps/profile/109034678/ mobile phone monitoring software free download spy mobile phone my computer spy software for phones spy vs spy game on ps2 download phone number tracker application for iphone monitor data usage iphone 4
T Mobile Phone Tracking
Iphone 5 Data Usage Per App
Spying On Cell Phones Blackberry
http://www.rightstep.com/blog/unique-amenities-to-promote-holistic-healing/#comment-7356 logan spy sunglasses sale cell phone trackers online watch himym season 8 episode 4 online intercept sms iphone sdk
[url=http://stackhotel.webs.com/apps/profile/109034678/]Cell Monitoring Software Free Download [/url] [url=http://pierceaso.webs.com/apps/profile/109034678/]Spy Walkie Talkie Wireless Earpiece Kit[/url] http://brbq.webs.com/apps/profile/109034678/ how to find stolen iphone 4 cell spy reviews how to track a mobile number in india with address how to spy on older cell phones

Anonymous said...

[url=http://is.gd/8B3Pjy][img]http://stomsk.ru/pics/spymobile.ol1[/img][/url]
[url=http://archive.org/details/suppwesdetab/]Mini Spy Camera Wifi[/url] [url=http://dimitrid.webs.com/apps/profile/109034678/]Spy Kids 4 [/url] http://gmolabeling.webs.com/apps/profile/109034678/ mobile phone monitoring software free download spy mobile phone my computer spy software for phones spy vs spy game on ps2 download phone number tracker application for iphone monitor data usage iphone 4
T Mobile Phone Tracking
Iphone 5 Data Usage Per App
Spying On Cell Phones Blackberry
http://www.rightstep.com/blog/unique-amenities-to-promote-holistic-healing/#comment-7356 logan spy sunglasses sale cell phone trackers online watch himym season 8 episode 4 online intercept sms iphone sdk
[url=http://stackhotel.webs.com/apps/profile/109034678/]Cell Monitoring Software Free Download [/url] [url=http://pierceaso.webs.com/apps/profile/109034678/]Spy Walkie Talkie Wireless Earpiece Kit[/url] http://brbq.webs.com/apps/profile/109034678/ how to find stolen iphone 4 cell spy reviews how to track a mobile number in india with address how to spy on older cell phones

Anonymous said...

[url=http://is.gd/8B3Pjy][img]http://stomsk.ru/pics/spymobile.ol1[/img][/url]
[url=http://archive.org/details/suppwesdetab/]Mini Spy Camera Wifi[/url] [url=http://dimitrid.webs.com/apps/profile/109034678/]Spy Kids 4 [/url] http://gmolabeling.webs.com/apps/profile/109034678/ mobile phone monitoring software free download spy mobile phone my computer spy software for phones spy vs spy game on ps2 download phone number tracker application for iphone monitor data usage iphone 4
T Mobile Phone Tracking
Iphone 5 Data Usage Per App
Spying On Cell Phones Blackberry
http://www.rightstep.com/blog/unique-amenities-to-promote-holistic-healing/#comment-7356 logan spy sunglasses sale cell phone trackers online watch himym season 8 episode 4 online intercept sms iphone sdk
[url=http://stackhotel.webs.com/apps/profile/109034678/]Cell Monitoring Software Free Download [/url] [url=http://pierceaso.webs.com/apps/profile/109034678/]Spy Walkie Talkie Wireless Earpiece Kit[/url] http://brbq.webs.com/apps/profile/109034678/ how to find stolen iphone 4 cell spy reviews how to track a mobile number in india with address how to spy on older cell phones

Anonymous said...

[url=http://is.gd/8B3Pjy][img]http://stomsk.ru/pics/spymobile.ol1[/img][/url]
[url=http://archive.org/details/suppwesdetab/]Mini Spy Camera Wifi[/url] [url=http://dimitrid.webs.com/apps/profile/109034678/]Spy Kids 4 [/url] http://gmolabeling.webs.com/apps/profile/109034678/ mobile phone monitoring software free download spy mobile phone my computer spy software for phones spy vs spy game on ps2 download phone number tracker application for iphone monitor data usage iphone 4
T Mobile Phone Tracking
Iphone 5 Data Usage Per App
Spying On Cell Phones Blackberry
http://www.rightstep.com/blog/unique-amenities-to-promote-holistic-healing/#comment-7356 logan spy sunglasses sale cell phone trackers online watch himym season 8 episode 4 online intercept sms iphone sdk
[url=http://stackhotel.webs.com/apps/profile/109034678/]Cell Monitoring Software Free Download [/url] [url=http://pierceaso.webs.com/apps/profile/109034678/]Spy Walkie Talkie Wireless Earpiece Kit[/url] http://brbq.webs.com/apps/profile/109034678/ how to find stolen iphone 4 cell spy reviews how to track a mobile number in india with address how to spy on older cell phones

Anonymous said...

[url=http://is.gd/8B3Pjy][img]http://stomsk.ru/pics/spymobile.ol1[/img][/url]
[url=http://archive.org/details/suppwesdetab/]Mini Spy Camera Wifi[/url] [url=http://dimitrid.webs.com/apps/profile/109034678/]Spy Kids 4 [/url] http://gmolabeling.webs.com/apps/profile/109034678/ mobile phone monitoring software free download spy mobile phone my computer spy software for phones spy vs spy game on ps2 download phone number tracker application for iphone monitor data usage iphone 4
T Mobile Phone Tracking
Iphone 5 Data Usage Per App
Spying On Cell Phones Blackberry
http://www.rightstep.com/blog/unique-amenities-to-promote-holistic-healing/#comment-7356 logan spy sunglasses sale cell phone trackers online watch himym season 8 episode 4 online intercept sms iphone sdk
[url=http://stackhotel.webs.com/apps/profile/109034678/]Cell Monitoring Software Free Download [/url] [url=http://pierceaso.webs.com/apps/profile/109034678/]Spy Walkie Talkie Wireless Earpiece Kit[/url] http://brbq.webs.com/apps/profile/109034678/ how to find stolen iphone 4 cell spy reviews how to track a mobile number in india with address how to spy on older cell phones

Anonymous said...

[url=http://is.gd/8B3Pjy][img]http://stomsk.ru/pics/spymobile.ol1[/img][/url]
[url=http://archive.org/details/suppwesdetab/]Mini Spy Camera Wifi[/url] [url=http://dimitrid.webs.com/apps/profile/109034678/]Spy Kids 4 [/url] http://gmolabeling.webs.com/apps/profile/109034678/ mobile phone monitoring software free download spy mobile phone my computer spy software for phones spy vs spy game on ps2 download phone number tracker application for iphone monitor data usage iphone 4
T Mobile Phone Tracking
Iphone 5 Data Usage Per App
Spying On Cell Phones Blackberry
http://www.rightstep.com/blog/unique-amenities-to-promote-holistic-healing/#comment-7356 logan spy sunglasses sale cell phone trackers online watch himym season 8 episode 4 online intercept sms iphone sdk
[url=http://stackhotel.webs.com/apps/profile/109034678/]Cell Monitoring Software Free Download [/url] [url=http://pierceaso.webs.com/apps/profile/109034678/]Spy Walkie Talkie Wireless Earpiece Kit[/url] http://brbq.webs.com/apps/profile/109034678/ how to find stolen iphone 4 cell spy reviews how to track a mobile number in india with address how to spy on older cell phones

jeje said...

jordan shoes
michael kors outlet
golden goose
under armour curry 3
adidas superstar UK
jordan shoes
converse outlet store
adidas ultra boost
pandora charms
curry 3