Flashback Version Query Posted by MOHAMMED ABDUL HAMEED on 8:33 AM

If we want to find out all the changes on the particular row ( Row Level ) then we can use the Flash Back Version Query.

SQL> show user;
USER is "HAMEED"

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
CITY                           TABLE

SQL> select * from city;

ID        NAME
-----     --------
1 Hyderabad
2 Bangalore
3 Pune

SQL> update city set name='Deccan' where id=1;

commit;

SQL> update city set name='Telangana' where id=1;

commit;

SQL> update city set name='India' where id=1;

commit;

SQL> select * from city;

ID        NAME
-----     --------
1 India
2 Bangalore
3 Pune

SQL> select versions_xid as xid,versions_startscn as start_scn,
          versions_endscn as end_scn,versions_operation as Operation,name 
          from city
          versions between scn minvalue and maxvalue
          where id=1;

   XID START_SCN END_SCN OPERATION NAME
-----------------------------------------------------------------------------------------------------------------
0B00040903000        346491                                           U India
0F00070040000        346479 346491                U Telangana
0E00050070000        346468               346479                 U                 Deccan
0D00020008000        341737                346468                 I                  Hyderabad

DML operations : U = Update,I= Insert,D=Delete

Present : 

SQL> select * from emp where id=1;

ID        NAME
-----     --------
1 India