Flashback version query in oracle 10g

flashback version query in oracle 10g

Flashback query was introduced in Oracle 9i. It provides a mechanism for viewing data as it existed at a particular point in time (a timestamp or SCN). With 10g Release 1, Oracle has extended flashback query to enable us to view different versions of our data in a given range between two timestamps or SCNs.

This article introduces the new flashback version query. It assumes that readers are familiar with flashback query concepts. For an overview, including the necessary privileges required, read this oracle-developer.net article.

sample data

For the examples in this article, we will use a scratch table and some dummy data. The table is created as follows.

SQL> CREATE TABLE fbt
2 ( x INTEGER
3 , y TIMESTAMP
4 , z VARCHAR2(30) );

Table created.
We can now populate the table and make several changes to the data. We will add a row, update it a couple of times and finally delete it. We will include a short pause between each DML operation, but capture the timestamp before and after the updates for use in later examples. All DML operations will be committed, because new versions of data are only recorded following a commit. We'll begin with a single-row insert.

SQL> EXEC DBMS_LOCK.SLEEP(10);

PL/SQL procedure successfully completed.

SQL> INSERT INTO fbt VALUES (1, LOCALTIMESTAMP, 'Initial population');

1 row created.

SQL> COMMIT;

Commit complete.
Note that we included a sleep before we added any data to the table. This is recommended by Oracle to avoid ORA-01466: unable to read data - table definition has changed (this will only be an issue if the table is subject to flashback queries as soon as it is created).

We can now update our sample data. Before we do, however, we'll capture the timestamp to use later in this article. We will also sleep for ten seconds. Again, our DML must be committed.

SQL> ALTER SESSION SET nls_timestamp_format = 'DD-MON-YYYY HH24:MI:SS.FF3';

Session altered.

SQL> EXEC DBMS_LOCK.SLEEP(10);

PL/SQL procedure successfully completed.

SQL> SELECT LOCALTIMESTAMP AS lower_bound FROM dual;

LOWER_BOUND
------------------------
10-AUG-2005 18:01:07.109

1 row selected.

SQL> UPDATE fbt SET y = LOCALTIMESTAMP, z = 'First update';

1 row updated.

SQL> COMMIT;

Commit complete.
Next, we update the data a second time and capture the timestamp after the commit.

SQL> EXEC DBMS_LOCK.SLEEP(10);

PL/SQL procedure successfully completed.

SQL> UPDATE fbt SET y = LOCALTIMESTAMP, z = 'Second update';

1 row updated.

SQL> COMMIT;

Commit complete.

SQL> SELECT LOCALTIMESTAMP AS upper_bound FROM dual;

UPPER_BOUND
------------------------
10-AUG-2005 18:01:17.125

1 row selected.
Finally, we will delete the data and commit the change.

SQL> EXEC DBMS_LOCK.SLEEP(10);

PL/SQL procedure successfully completed.

SQL> DELETE FROM fbt WHERE x = 1;

1 row deleted.

SQL> COMMIT;

Commit complete.
Following the sample data population, we should have no data in the FBT table, which we can verify as follows.

SQL> SELECT * FROM fbt;

no rows selected
flashback version query

Despite the fact that there is no data in FBT, we can now run some flashback version queries against it. This will enable us to view the data as it evolved between commits. Flashback version query is invoked using the new VERSIONS BETWEEN extension to the FROM clause. It takes two forms as follows:

VERSIONS BETWEEN TIMESTAMP [lower bound] AND [upper bound]; or
VERSIONS BETWEEN SCN [lower bound] AND [lower bound].
The lower and upper boundaries can either be specific timestamps/SCNs or the keywords MINVALUE and MAXVALUE. These keywords instruct Oracle to retrieve all available data versions. The age of the data available is determined by the undo_retention parameter. For our first flashback version query, we will attempt to retrieve all available data.

SQL> ALTER SESSION SET nls_timestamp_format = 'DD-MON-YYYY HH24:MI:SS.FF3';

Session altered.

SQL> SELECT x, y, z
2 FROM fbt VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE
3 ORDER BY
4 y;

X Y Z
---------- ------------------------- -----------------------
1 10-AUG-2005 18:00:57.078 Initial population
1 10-AUG-2005 18:01:07.109 First update
1 10-AUG-2005 18:01:17.125 Second update
1 10-AUG-2005 18:01:17.125 Second update

4 rows selected.
We have seemingly generated four rows of data from one source record. What we are seeing, however, is the evolution of this single record in terms of all the values it has held over time (depending on its presence in the undo segments). However, we can see that the second update entry appears twice, yet we have no way of identifying why from the above output. Flashback version query therefore includes several pseudo-columns to describe each version of our data, which we can now use to determine the actual operations and change times.

versions pseudo-columns

As stated above, Oracle provides a variety of metadata with each version of our data. The metadata is exposed via a number of pseudo-columns that we can use with our flashback version queries. These pseudo-columns are as follows:

VERSIONS_STARTTIME (start timestamp of version);
VERSIONS_STARTSCN (start SCN of version);
VERSIONS_ENDTIME (end timestamp of version);
VERSIONS_ENDSCN (end SCN of version);
VERSIONS_XID (transaction ID of version); and
VERSIONS_OPERATION (DML operation of version).
We can now include some of these pseudo-columns in our flashback version query as follows. Note the SCN metadata is excluded as we are using timestamps for the examples.

SQL> SELECT z
2 , VERSIONS_STARTTIME
3 , VERSIONS_ENDTIME
4 , VERSIONS_XID
5 , VERSIONS_OPERATION
6 FROM fbt VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE
7 ORDER BY
8 VERSIONS_ENDTIME;
Z VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID VERSIONS_OPERATION
-------------------- ------------------------- ------------------------- ---------------- ------------------
Initial population 10-AUG-2005 18:00:53.000 10-AUG-2005 18:01:05.000 040026008A010000 I
First update 10-AUG-2005 18:01:05.000 10-AUG-2005 18:01:14.000 040029008A010000 U
Second update 10-AUG-2005 18:01:14.000 10-AUG-2005 18:01:26.000 040027008A010000 U
Second update 10-AUG-2005 18:01:26.000 040028008A010000 D

4 rows selected.
This explains why we were seeing the second update row twice. If we look at the VERSIONS_OPERATION column, we can see that the second appearance of the final update record is actually the delete operation against it (specified by 'D'). Without the versions metadata, the Y timestamp column was actually confusing us into thinking we had two versions of the same record at the same time.

The metadata also gives us an indication that the delete operation was the final version of this data. The end timestamp of the version is NULL which tells us that there is no superceding record.

Interestingly, if we had not included a sleep between creating the FBT table and adding the single record, it would be likely (based on observations) that all VERSIONS_* pseudo-columns (except the ENDTIME and ENDSCN) would be NULL for the insert record.

flashback transaction query

Oracle has provided a new view, FLASHBACK_TRANSACTION_QUERY, to provide more information about the data versions. This includes the SQL required to reverse each change. Queries against this view are documented as "flashback transaction queries" and require the SELECT ANY TRANSACTION system privilege. The view definition is as follows.

SQL> desc flashback_transaction_query
Name Null? Type
----------------------------- -------- --------------------
XID RAW(8)
START_SCN NUMBER
START_TIMESTAMP DATE
COMMIT_SCN NUMBER
COMMIT_TIMESTAMP DATE
LOGON_USER VARCHAR2(30)
UNDO_CHANGE# NUMBER
OPERATION VARCHAR2(32)
TABLE_NAME VARCHAR2(256)
TABLE_OWNER VARCHAR2(32)
ROW_ID VARCHAR2(19)
UNDO_SQL VARCHAR2(4000)
The VERSIONS_XID pseudo-column gives us the key to this view (the XID column) for specific versions of data. Rather than filter on XID, we will look at all records currently available for our FBT table, concentrating on the more interesting UNDO_SQL column.

SQL> SELECT xid
2 , operation
3 , undo_sql
4 FROM flashback_transaction_query
5 WHERE table_owner = USER
6 AND table_name = 'FBT'
7 ORDER BY
8 start_timestamp;

XID OPERATION UNDO_SQL
---------------- --------- ------------------------------------------------------------
040026008A010000 INSERT delete from "SCOTT"."FBT" where ROWID = 'AAANCeAAEAAAAuXAAA'
;

040029008A010000 UPDATE update "SCOTT"."FBT" set "Y" = TO_TIMESTAMP('10-AUG-2005 18:
00:57.000'), "Z" = 'Initial population' where ROWID = 'AAANC
eAAEAAAAuXAAA';

040027008A010000 UPDATE update "SCOTT"."FBT" set "Y" = TO_TIMESTAMP('10-AUG-2005 18:
01:07.000'), "Z" = 'First update' where ROWID = 'AAANCeAAEAA
AAuXAAA';

040028008A010000 DELETE insert into "SCOTT"."FBT"("X","Y","Z") values ('1',TO_TIMEST
AMP('10-AUG-2005 18:01:17.000'),'Second update');


4 rows selected.
The UNDO_SQL column shows us the reversal of every change we made to our sample record. Remember that we followed a sequence of INSERT-UPDATE-UPDATE-DELETE. The values in the OPERATION column show us this, reading from the top-down. The reversal of this sequence can be viewed by reading the UNDO_SQL column from the bottom-up. For recovery purposes this can be quite useful. Note that the user responsible for the change is also available.

versions between explicit ranges

So far, we have issued flashback version queries using the MINVALUE and MAXVALUE range boundaries. As noted earlier in the article, we can also supply specific timestamp or SCN ranges. Remember that we captured the timestamps before and after the updates of our data? We will use these to limit the data versions requested below. One restriction with using specific timestamps or SCNs is that they must be within the boundaries of the undo_retention parameter. Attempting to flashback to a version older than approximately query time-undo_retention will result in ORA-30052: invalid lower limit snapshot expression.

Note that in the following examples, the ALTER SESSION statement is included for convenience, to save having to supply a long format mask in the TO_TIMESTAMP calls.

SQL> ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY HH24:MI:SS.FF3';

Session altered.

SQL> SELECT x
2 , y
3 , z
4 , VERSIONS_OPERATION
5 FROM fbt VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('10-AUG-2005 18:01:07.109')
6 AND TO_TIMESTAMP('10-AUG-2005 18:01:17.125')
7 ORDER BY
8 VERSIONS_ENDTIME;

X Y Z VERSIONS_OPERATION
--- ------------------------- -------------------- ------------------
1 10-AUG-2005 18:00:57.078 Initial population
1 10-AUG-2005 18:01:07.109 First update U
1 10-AUG-2005 18:01:17.125 Second update U

3 rows selected.
Remember that we took the timestamp for the lower boundary before our first update. We can see this from the above version query. At this time, there was already a version of our data in existence (the initial insert). The VERSIONS_OPERATION pseudo-column is NULL for this record because the change had already occurred prior to the lower timestamp boundary. Our two updates occurred within the timestamp range, however, and the VERSIONS_OPERATION column shows us this. This is a useful tracking mechanism that enables us to distinguish between changes and existing data.

a note on timestamps and scns

As a final note, it is very simple to switch between SCNs and timestamps, should the need arise. Oracle 10g provides two conversion functions, TIMESTAMP_TO_SCN and SCN_TO_TIMESTAMP for this purpose. Timestamps are mapped to SCNs with a precision of approximately 3 seconds (in 9i this was 5 minutes). Most readers will be aware of the SYSTIMESTAMP and LOCALTIMESTAMP functions to capture timestamps, but we can also capture SCNs using the DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER function. We will complete this article with an example of each.

SQL> ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY HH24:MI:SS.FF3';

Session altered.

SQL> SELECT SYSTIMESTAMP
2 , LOCALTIMESTAMP
3 , DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER AS scn
4 FROM dual;

SYSTIMESTAMP LOCALTIMESTAMP SCN
----------------------------------- ------------------------- ----------
10-AUG-05 18.08.04.078000 +00:00 10-AUG-2005 18:08:04.078 1408640

1 row selected.

SQL> SELECT SCN_TO_TIMESTAMP(1408640) AS ts
2 FROM dual;

TS
-------------------------
10-AUG-2005 18:08:03.000

1 row selected.

SQL> SELECT TIMESTAMP_TO_SCN('10-AUG-2005 18:08:03.000') AS scn
2 FROM dual;

SCN
----------
1408640

1 row selected.