Flashback Version Query is used to get metadata and historical data for a particular time interval. This is done by VERSIONS BETWEEN clause with time interval being either timestamps or SCN using the following pseudo columns:
- VERSIONS_START
- VERSIONS_END
- VERSIONS_XID
- VERSIONS_OPERATIONS
Return columns are from START date and up until (not including) the END date.
EXAMPLE:
- Logon to your Oracle 12c database as user scott.
[oracle@linux2 flash_recovery_area]$ sqlplus scott/password
SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 6 07:33:45 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Wed Jan 04 2015 07:45:52 -05:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
SQL>
- Create a table fb_scott.
SQL> create table fb_test(fname varchar2(30), amt number);
Table created.
- Select current system timestamp
select systimestamp from dual;
SQL> select systimestamp from dual;
SYSTIMESTAMP
—————————————————————————
06-JAN-15 07.44.13.923919 AM -05:00
SQL>
- Insert values into your new table fb_test and commit insert.
insert into scott.fb_test(fname,amt) values(‘Larry’,111);
commit;
SQL> insert into scott.fb_test(fname,amt) values(‘Larry’,111);
commit;
1 row created.
SQL>
Commit complete.
SQL>
- Now perform 3 update operations on table fb_test and commit between each update.
update scott.fb_test set amt=222 where fname=’Larry’;
commit;
update scott.fb_test set amt=333 where fname=’Larry’;
commit;
update scott.fb_test set amt=444 where fname=’Larry’;
commit;
SQL> update scott.fb_test set amt=222 where fname=’Larry’;
commit;
update scott.fb_test set amt=333 where fname=’Larry’;
commit;
update scott.fb_test set amt=444 where fname=’Larry’;
commit;
1 row updated.
SQL>
Commit complete.
SQL>
1 row updated.
SQL>
Commit complete.
SQL>
1 row updated.
SQL>
Commit complete.
SQL>
- Finally select current systimestamp from dual.
select systimestamp from dual;
SQL> select systimestamp from dual;
SYSTIMESTAMP
—————————————————————————
06-JAN-15 07.45.55.308034 AM -05:00
SQL>
- Select the past versions of your table fb_test using the versions between clause.
select versions_starttime, versions_endtime, versions_xid, versions_operation as op, amt
from scott.fb_test versions between timestamp to_timestamp (’06-JAN-15 07:44:13′, ‘DD-MON-YY HH24:MI:SS’) and to_timestamp (’06-JAN-15 07:45:55′, ‘DD-MON-YY HH24:MI:SS’) where fname=’Larry’;
Results:
SQL> select versions_starttime, versions_endtime, versions_xid, versions_operation as op, amt
from scott.fb_test versions between timestamp to_timestamp (’06-JAN-15 07:44:13′, ‘DD-MON-YY HH24:MI:SS’) and to_timestamp (’06-JAN-15 07:45:55′, ‘DD-MON-YY HH24:MI:SS’) where fname=’Larry’;
2
VERSIONS_STARTTIME
—————————————————————————
VERSIONS_ENDTIME
—————————————————————————
VERSIONS_XID O AMT
—————- – ———-
06-JAN-15 07.45.30 AM
090009006F0B0000 U 444
06-JAN-15 07.45.30 AM
06-JAN-15 07.45.30 AM
02001500AF0B0000 U 333
VERSIONS_STARTTIME
—————————————————————————
VERSIONS_ENDTIME
—————————————————————————
VERSIONS_XID O AMT
—————- – ———-
06-JAN-15 07.45.27 AM
06-JAN-15 07.45.30 AM
04000A00680A0000 U 222
06-JAN-15 07.45.12 AM
06-JAN-15 07.45.27 AM
VERSIONS_STARTTIME
—————————————————————————
VERSIONS_ENDTIME
—————————————————————————
VERSIONS_XID O AMT
—————- – ———-
080021001F0C0000 I 111
SQL>
- This completes viewing previous versions of a table in Oracle 12c.
Larry Catt
OCP