Using Flashback Versions Query

Using Flashback Versions Query

===============================

 

(1) Execute update statments to generate changes

 

scott@TOY10G> update accounts set balance=2000 where account_no=1;

 

1 row updated.

 

scott@TOY10G> commit;

 

Commit complete.

 

scott@TOY10G> update accounts set balance=4000 where account_no=1;

 

1 row updated.

 

scott@TOY10G> commit;

 

Commit complete.

 

scott@TOY10G> update accounts set balance=9000 where account_no=1;

 

1 row updated.

 

scott@TOY10G> commit;

 

Commit complete.

 

scott@TOY10G>

 

 

(2) Query using versions between

 

scott@TOY10G> select balance from accounts

  2  versions between

  3  scn minvalue and maxvalue

  4  where account_no=1;

 

   BALANCE

----------

      6000

      4000

      2000

77195.4526

 

 

(3) Using timestamp 

 

select balance from accounts

versions between timestamp

to_timestamp('14-AUG-2007 19:15:01','DD-MON-YYYY HH24:MI:SS') and

to_timestamp('14-AUG-2007 21:29:01','DD-MON-YYYY HH24:MI:SS')

where account_no=1;

 

 

(4) pseudocolumns

 

scott@TOY10G> select to_char(versions_starttime,'DD-MON-YYYY HH24:MI') "START DATE",

to_char (versions_endtime, 'DD-MON-YYYY HH24:MI') "END DATE",

versions_xid,

versions_operation,

balance

from accounts

versions between scn

minvalue and maxvalue

where account_no=1;  2    3    4    5    6    7    8    9 

 

START DATE        END DATE          VERSIONS_XID     V    BALANCE

----------------- ----------------- ---------------- - ----------

14-AUG-2007 21:27                   000500110000039F U       9000

14-AUG-2007 21:27 14-AUG-2007 21:27 0007001B0000116F U       4000

14-AUG-2007 21:27 14-AUG-2007 21:27 0001002100000396 U       2000

14-AUG-2007 20:56 14-AUG-2007 21:27 0007001E0000116C U       6000

14-AUG-2007 20:56 14-AUG-2007 20:56 0002001C00000390 U       4000

14-AUG-2007 20:56 14-AUG-2007 20:56 0007000E0000116C U       2000

                  14-AUG-2007 20:56                    77195.4526

 

Note:

 

There are several new pseudocolumns that help you work with the Flashback Versions Query:

_ VERSIONS_STARTTIME

_ VERSIONS_STARTSCN

_ VERSIONS_ENDTIME

_ VERSIONS_ENDSCN

_ VERSIONS_XID

_ VERSIONS_OPERATION