One of the very cool technologies that Oracle Database has is the concept of Flashback Query, the ability to look “into the past” at the state of some data in a table.
It is part of a whole suite of Flashback technologies. If you’re interested in an overview of all of them, check out my playlist on this topic
One component of Flashback Query is the concept of a version of a row. Every time you commit a transaction, you have effectively created a new version of that row which is valid for particular range of time. The VERSIONS BETWEEN syntax on Flashback Query lets us see all of those versions for a given row.
For example, I’ll add a row to a table and commit that INSERT. That is one version of the row. Then I’ll update it to yield a second version of the row.
SQL> create table t ( x int );
Table created.
SQL> insert into t values (1);
1 row created.
SQL> commit;
Commit complete.
SQL> update t
2 set x = 2;
1 row updated.
SQL> commit;
Commit complete.
Now we can see both versions of the single row using VERSIONS BETWEEN. To reiterate, this is one true row in the table, just two versions over time of that row.
SQL> select
2 decode( versions_operation
3 , 'I', 'insert'
4 , 'U', 'update'
5 , 'D', 'delete'
6 , 'original'
7 ) op
8 ,versions_xid
9 ,versions_startscn
10 ,versions_endscn
11 ,rowid rid
12 from t
13 versions between scn minvalue and maxvalue;
OP VERSIONS_XID VERSIONS_STARTSCN VERSIONS_ENDSCN RID
-------- ---------------- ----------------- --------------- ------------------
update 0B0011001B3F0000 1.6331E+13 AAAuWWAAMAAAYJ7AAA
insert 02001C00C4230100 1.6331E+13 1.6331E+13 AAAuWWAAMAAAYJ7AAA
If the rows in the table were created at table creation time, and hence never inserted, then the operation is null, for example, a CTAS, as we can see below
SQL> create table t as select 1 x from dual;
Table created.
SQL> select
2 decode( versions_operation
3 , 'I', 'insert'
4 , 'U', 'update'
5 , 'D', 'delete'
6 , 'original'
7 ) op
8 ,versions_xid
9 ,versions_startscn
10 ,versions_endscn
11 ,rowid rid
12 from t
13 versions between scn minvalue and maxvalue;
OP VERSIONS_XID VERSIONS_STARTSCN VERSIONS_ENDSCN RID
-------- ---------------- ----------------- --------------- ------------------
original AAAuWXAAMAAAYJ7AAA
You can probably already see the great potential here for this feature when it comes to things like unit testing. I can run multiple transactions through a set of rows, and then dig back into the versions of those rows to ensure that each transaction did the correct thing, and explore the evolution of a row as it is changed by my application.
But here is the motivation for this post. You need to adhere to one very important rule when using the feature.
To demonstrate, here is a fictional unit test style script that aims to use VERSIONS BETWEEN to track changes. I will
- create table T as copy of SCOTT.EMP. That will yield 14 rows, each as “original” versions
- I will then update each of the 14 rows 3 times, each in an individual transaction. That will yield 42 updates.
Thus at the end of the exercise, I should have 14+42 = 56 versions in total of the rows in this table. Let me now test that.
SQL> create table t as select * from scott.emp;
Table created.
SQL> alter table t add primary key ( empno );
Table altered.
SQL> begin
2 update t set sal = sal + 1 where empno = 7788; commit;
3 update t set sal = sal + 1 where empno = 7654; commit;
4 update t set sal = sal + 1 where empno = 7900; commit;
5 update t set sal = sal + 1 where empno = 7566; commit;
6 update t set sal = sal + 1 where empno = 7698; commit;
7 update t set sal = sal + 1 where empno = 7934; commit;
8 update t set sal = sal + 1 where empno = 7369; commit;
9 update t set sal = sal + 1 where empno = 7876; commit;
10 update t set sal = sal + 1 where empno = 7521; commit;
11 update t set sal = sal + 1 where empno = 7782; commit;
12 update t set sal = sal + 1 where empno = 7902; commit;
13 update t set sal = sal + 1 where empno = 7499; commit;
14 update t set sal = sal + 1 where empno = 7844; commit;
15 update t set sal = sal + 1 where empno = 7839; commit;
16 update t set sal = sal + 1 where empno = 7788; commit;
17 update t set sal = sal + 1 where empno = 7654; commit;
18 update t set sal = sal + 1 where empno = 7900; commit;
19 update t set sal = sal + 1 where empno = 7566; commit;
20 update t set sal = sal + 1 where empno = 7698; commit;
21 update t set sal = sal + 1 where empno = 7934; commit;
22 update t set sal = sal + 1 where empno = 7369; commit;
23 update t set sal = sal + 1 where empno = 7876; commit;
24 update t set sal = sal + 1 where empno = 7521; commit;
25 update t set sal = sal + 1 where empno = 7782; commit;
26 update t set sal = sal + 1 where empno = 7902; commit;
27 update t set sal = sal + 1 where empno = 7499; commit;
28 update t set sal = sal + 1 where empno = 7844; commit;
29 update t set sal = sal + 1 where empno = 7839; commit;
30 update t set sal = sal + 1 where empno = 7788; commit;
31 update t set sal = sal + 1 where empno = 7654; commit;
32 update t set sal = sal + 1 where empno = 7900; commit;
33 update t set sal = sal + 1 where empno = 7566; commit;
34 update t set sal = sal + 1 where empno = 7698; commit;
35 update t set sal = sal + 1 where empno = 7934; commit;
36 update t set sal = sal + 1 where empno = 7369; commit;
37 update t set sal = sal + 1 where empno = 7876; commit;
38 update t set sal = sal + 1 where empno = 7521; commit;
39 update t set sal = sal + 1 where empno = 7782; commit;
40 update t set sal = sal + 1 where empno = 7902; commit;
41 update t set sal = sal + 1 where empno = 7499; commit;
42 update t set sal = sal + 1 where empno = 7844; commit;
43 update t set sal = sal + 1 where empno = 7839; commit;
44 end;
45 /
PL/SQL procedure successfully completed.
and now we check the number of versions we tracked
SQL> select
2 decode( versions_operation
3 , 'I', 'insert'
4 , 'U', 'update'
5 , 'D', 'delete'
6 , 'original'
7 ) op
8 ,versions_xid
9 ,versions_startscn
10 ,versions_endscn
11 ,rowid rid
12 from t
13 versions between scn minvalue and maxvalue;
OP VERSIONS_XID VERSIONS_STARTSCN VERSIONS_ENDSCN RID
-------- ---------------- ----------------- --------------- ------------------
original AAAuWUAAMAAAYJ7AAA
original AAAuWUAAMAAAYJ7AAB
original AAAuWUAAMAAAYJ7AAC
original AAAuWUAAMAAAYJ7AAD
original AAAuWUAAMAAAYJ7AAE
original AAAuWUAAMAAAYJ7AAF
original AAAuWUAAMAAAYJ7AAG
original AAAuWUAAMAAAYJ7AAH
original AAAuWUAAMAAAYJ7AAI
original AAAuWUAAMAAAYJ7AAJ
original AAAuWUAAMAAAYJ7AAK
original AAAuWUAAMAAAYJ7AAL
original AAAuWUAAMAAAYJ7AAM
original AAAuWUAAMAAAYJ7AAN
14 rows selected.
Uh oh! What happened to our updates?
Important Note: Once you create a new table, some processing occurs in the background in the Oracle Database that will subsequently allow for changes to be correctly tracked. If you run transactions immediately on a table after creating it, some of those may be missed when it comes to later VERSIONS BETWEEN queries.
This is noted in the docs
Lets now repeat the same test, but this time I will pause for a while after creating my table.
SQL> create table t as select * from scott.emp;
Table created.
SQL> alter table t add primary key ( empno );
Table altered.
SQL> host sleep 30
SQL>
SQL> begin
2 update t set sal = sal + 1 where empno = 7788; commit;
3 update t set sal = sal + 1 where empno = 7654; commit;
4 update t set sal = sal + 1 where empno = 7900; commit;
5 update t set sal = sal + 1 where empno = 7566; commit;
6 update t set sal = sal + 1 where empno = 7698; commit;
7 update t set sal = sal + 1 where empno = 7934; commit;
8 update t set sal = sal + 1 where empno = 7369; commit;
9 update t set sal = sal + 1 where empno = 7876; commit;
10 update t set sal = sal + 1 where empno = 7521; commit;
11 update t set sal = sal + 1 where empno = 7782; commit;
12 update t set sal = sal + 1 where empno = 7902; commit;
13 update t set sal = sal + 1 where empno = 7499; commit;
14 update t set sal = sal + 1 where empno = 7844; commit;
15 update t set sal = sal + 1 where empno = 7839; commit;
16 update t set sal = sal + 1 where empno = 7788; commit;
17 update t set sal = sal + 1 where empno = 7654; commit;
18 update t set sal = sal + 1 where empno = 7900; commit;
19 update t set sal = sal + 1 where empno = 7566; commit;
20 update t set sal = sal + 1 where empno = 7698; commit;
21 update t set sal = sal + 1 where empno = 7934; commit;
22 update t set sal = sal + 1 where empno = 7369; commit;
23 update t set sal = sal + 1 where empno = 7876; commit;
24 update t set sal = sal + 1 where empno = 7521; commit;
25 update t set sal = sal + 1 where empno = 7782; commit;
26 update t set sal = sal + 1 where empno = 7902; commit;
27 update t set sal = sal + 1 where empno = 7499; commit;
28 update t set sal = sal + 1 where empno = 7844; commit;
29 update t set sal = sal + 1 where empno = 7839; commit;
30 update t set sal = sal + 1 where empno = 7788; commit;
31 update t set sal = sal + 1 where empno = 7654; commit;
32 update t set sal = sal + 1 where empno = 7900; commit;
33 update t set sal = sal + 1 where empno = 7566; commit;
34 update t set sal = sal + 1 where empno = 7698; commit;
35 update t set sal = sal + 1 where empno = 7934; commit;
36 update t set sal = sal + 1 where empno = 7369; commit;
37 update t set sal = sal + 1 where empno = 7876; commit;
38 update t set sal = sal + 1 where empno = 7521; commit;
39 update t set sal = sal + 1 where empno = 7782; commit;
40 update t set sal = sal + 1 where empno = 7902; commit;
41 update t set sal = sal + 1 where empno = 7499; commit;
42 update t set sal = sal + 1 where empno = 7844; commit;
43 update t set sal = sal + 1 where empno = 7839; commit;
44 end;
45 /
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT DECODE( versions_operation
2 , 'I', 'Insert'
3 , 'U', 'Update'
4 , 'D', 'Delete'
5 , 'Original'
6 ) "Operation"
7 , versions_xid
8 , versions_startscn
9 , versions_endscn
10 ,rowid row_id
11 ,TIMESTAMP_TO_SCN(systimestamp) now_scn
12 FROM t
13 VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE;
Operatio VERSIONS_XID VERSIONS_STARTSCN VERSIONS_ENDSCN ROW_ID NOW_SCN
-------- ---------------- ----------------- --------------- ------------------ ----------
Update 0B002000173F0000 1.6331E+13 AAAuWSAAMAAAYJ7AAI 1.6331E+13
Update 02000A00AE230100 1.6331E+13 AAAuWSAAMAAAYJ7AAJ 1.6331E+13
Update 04001400021C0100 1.6331E+13 AAAuWSAAMAAAYJ7AAB 1.6331E+13
Update 01000100AD250100 1.6331E+13 AAAuWSAAMAAAYJ7AAM 1.6331E+13
Update 0B0001000F3F0000 1.6331E+13 AAAuWSAAMAAAYJ7AAG 1.6331E+13
Update 0B000700153F0000 1.6331E+13 AAAuWSAAMAAAYJ7AAC 1.6331E+13
Update 08001B00BD290100 1.6331E+13 AAAuWSAAMAAAYJ7AAK 1.6331E+13
Update 0B001900FE3E0000 1.6331E+13 AAAuWSAAMAAAYJ7AAA 1.6331E+13
Update 09000F00D2590100 1.6331E+13 AAAuWSAAMAAAYJ7AAN 1.6331E+13
Update 02000F00A3230100 1.6331E+13 AAAuWSAAMAAAYJ7AAF 1.6331E+13
Update 0B000D00113F0000 1.6331E+13 AAAuWSAAMAAAYJ7AAD 1.6331E+13
Update 01001A00AA250100 1.6331E+13 AAAuWSAAMAAAYJ7AAL 1.6331E+13
Update 0B0021001A3F0000 1.6331E+13 AAAuWSAAMAAAYJ7AAE 1.6331E+13
Update 0B001100163F0000 1.6331E+13 AAAuWSAAMAAAYJ7AAH 1.6331E+13
Update 0B000800093F0000 1.6331E+13 1.6331E+13 AAAuWSAAMAAAYJ7AAI 1.6331E+13
Update 05001200EE200100 1.6331E+13 1.6331E+13 AAAuWSAAMAAAYJ7AAJ 1.6331E+13
Update 08002100C0290100 1.6331E+13 1.6331E+13 AAAuWSAAMAAAYJ7AAB 1.6331E+13
Update 05001E00DD200100 1.6331E+13 1.6331E+13 AAAuWSAAMAAAYJ7AAM 1.6331E+13
Update 0B000900073F0000 1.6331E+13 1.6331E+13 AAAuWSAAMAAAYJ7AAG 1.6331E+13
Update 0A001D0070FD0700 1.6331E+13 1.6331E+13 AAAuWSAAMAAAYJ7AAC 1.6331E+13
Update 05000200D8200100 1.6331E+13 1.6331E+13 AAAuWSAAMAAAYJ7AAK 1.6331E+13
Update 0B0005000D3F0000 1.6331E+13 1.6331E+13 AAAuWSAAMAAAYJ7AAA 1.6331E+13
Update 09001300175A0100 1.6331E+13 1.6331E+13 AAAuWSAAMAAAYJ7AAN 1.6331E+13
Update 0B001C00643E0000 1.6331E+13 1.6331E+13 AAAuWSAAMAAAYJ7AAF 1.6331E+13
Update 06001F0063290100 1.6331E+13 1.6331E+13 AAAuWSAAMAAAYJ7AAD 1.6331E+13
Update 09000C000B5A0100 1.6331E+13 1.6331E+13 AAAuWSAAMAAAYJ7AAL 1.6331E+13
Update 0B000600173F0000 1.6331E+13 1.6331E+13 AAAuWSAAMAAAYJ7AAE 1.6331E+13
Update 0B000200133F0000 1.6331E+13 1.6331E+13 AAAuWSAAMAAAYJ7AAH 1.6331E+13
Update 0B001B008B3E0000 1.6331E+13 1.6331E+13 AAAuWSAAMAAAYJ7AAI 1.6331E+13
Update 08000600B9290100 1.6331E+13 1.6331E+13 AAAuWSAAMAAAYJ7AAJ 1.6331E+13
Update 0B000F00143F0000 1.6331E+13 1.6331E+13 AAAuWSAAMAAAYJ7AAB 1.6331E+13
Update 0B001200023F0000 1.6331E+13 1.6331E+13 AAAuWSAAMAAAYJ7AAM 1.6331E+13
Update 0B001F00123F0000 1.6331E+13 1.6331E+13 AAAuWSAAMAAAYJ7AAG 1.6331E+13
Update 0B001700173F0000 1.6331E+13 1.6331E+13 AAAuWSAAMAAAYJ7AAC 1.6331E+13
Update 01001500B0250100 1.6331E+13 1.6331E+13 AAAuWSAAMAAAYJ7AAK 1.6331E+13
Update 0B001300063F0000 1.6331E+13 1.6331E+13 AAAuWSAAMAAAYJ7AAA 1.6331E+13
Update 0B0003000E3F0000 1.6331E+13 1.6331E+13 AAAuWSAAMAAAYJ7AAN 1.6331E+13
Update 06000B0079290100 1.6331E+13 1.6331E+13 AAAuWSAAMAAAYJ7AAF 1.6331E+13
Update 0B000400FE3E0000 1.6331E+13 1.6331E+13 AAAuWSAAMAAAYJ7AAD 1.6331E+13
Update 060002007B290100 1.6331E+13 1.6331E+13 AAAuWSAAMAAAYJ7AAL 1.6331E+13
Update 01001E009C250100 1.6331E+13 1.6331E+13 AAAuWSAAMAAAYJ7AAE 1.6331E+13
Update 0B001E00153F0000 1.6331E+13 1.6331E+13 AAAuWSAAMAAAYJ7AAH 1.6331E+13
Original 1.6331E+13 AAAuWSAAMAAAYJ7AAA 1.6331E+13
Original 1.6331E+13 AAAuWSAAMAAAYJ7AAB 1.6331E+13
Original 1.6331E+13 AAAuWSAAMAAAYJ7AAC 1.6331E+13
Original 1.6331E+13 AAAuWSAAMAAAYJ7AAD 1.6331E+13
Original 1.6331E+13 AAAuWSAAMAAAYJ7AAE 1.6331E+13
Original 1.6331E+13 AAAuWSAAMAAAYJ7AAF 1.6331E+13
Original 1.6331E+13 AAAuWSAAMAAAYJ7AAG 1.6331E+13
Original 1.6331E+13 AAAuWSAAMAAAYJ7AAH 1.6331E+13
Original 1.6331E+13 AAAuWSAAMAAAYJ7AAI 1.6331E+13
Original 1.6331E+13 AAAuWSAAMAAAYJ7AAJ 1.6331E+13
Original 1.6331E+13 AAAuWSAAMAAAYJ7AAK 1.6331E+13
Original 1.6331E+13 AAAuWSAAMAAAYJ7AAL 1.6331E+13
Original 1.6331E+13 AAAuWSAAMAAAYJ7AAM 1.6331E+13
Original 1.6331E+13 AAAuWSAAMAAAYJ7AAN 1.6331E+13
56 rows selected.
As we see, we got the expected 56 versions back from our query.
If you are using this feature, please ensure you are mindful of this, otherwise you probably will get incorrect results from your VERSIONS BETWEEN queries.
Learning is not a spectator sport 
Got some thoughts? Leave a comment