Flashback Versions Query

Before Oracle 10g, it was not possible to view series of changes made to the table in the past. Oracle introduced ‘Flashback Query’ feature in oracle 9i, which gave a view of the table at very specific time in the past. To view all the changes made to a specific row between two time intervals, Oracle introduced ‘Flashback Versions Query’ in 10g. Using this feature, we can see all the versions of the row (changes made to the row) between specific time intervals. Whenever commit happens, new version of row gets created.

First let us create a table. If you already have table with the same name, change all the occurrences of the table name with some other name.

Flashback versions query uses VERSIONS BETWEEN clause to return each ‘version of the row’ for a specific time interval along with some other pseudo columns. Pseudo columns are

* Versions_StartSCN – Starting SCN of a row version.
* Versions_EndSCN – SCN when a row version got expired.
* Versions_StartTime – Starting time of a row version.
* Versions_EndTime – Ending time when a row version got expired.
* Versions_XID – transaction ID that created a row version
* Versions_Operation – Insert/ Update/ Delete operation performed by a transaction.

For a detailed explanation of pseudo column, please refer to the Oracle Database Application Developer Guide for 10g.

Run following query to see the result set.

SELECT versions_Startscn, versions_endscn,
versions_xid, CASE
WHEN versions_operation = ‘I’ THEN ‘INSERT’
WHEN versions_operation = ‘U’ THEN ‘UPDATE’
WHEN versions_operation = ‘D’ THEN ‘DELETE’
END AS Operation,
tran_amt
FROM test_ac
VERSIONS BETWEEN TIMESTAMP minvalue AND maxvalue
ORDER BY versions_Startscn;

We can also query the versions_starttime and versions_endtime instead of SCN. Run following query to see the creation and expiration time of each row version. Also instead of minvalue and maxvalue, we can use interval same way as we use it in regular flash back query. Following is the example.

SELECT versions_Starttime, versions_endtime,
versions_xid, CASE
WHEN versions_operation = ‘I’ THEN ‘INSERT’
WHEN versions_operation = ‘U’ THEN ‘UPDATE’
WHEN versions_operation = ‘D’ THEN ‘DELETE’
END AS Operation,
tran_amt
FROM test_ac
VERSIONS BETWEEN TIMESTAMP systimestamp – interval ’10’ minute and systimestamp
ORDER BY versions_Startscn;

In above query, we are requesting all the changes made to the row in last 10 minutes. Output is shown below.

Using flashback transaction query, we can obtain transaction information including SQL code fired by transaction, to undo the changes made by transaction. A flashback transaction query is a query on the view FLASHBACK_TRANSACTION_QUERY. We can use versions_xid column from above queries to query the view and obtain the transaction information. Run following query to get the transaction details. Output is shown below the query.

If you look at UNDO_SQL column carefully, you will see that update statement, sets the value to 5000 and not to 7000 because it displays the SQL to undo the changes made by the transaction. In this case, transaction changed value of tran_amt column from 5000 to 7000 and hence UNDO_SQL column shows the SQL to revert back the change. Logon_user column shows the user responsible for the change.

Warning

Flashback query uses Oracle’s multiversion read-consistency to retrieve the data by applying undo as needed. So data will be available only for the time specified by UNDO_RETENTION parameter in the database. It will not return the historical data, if time difference exceeds the time defined by UNDO_RETENTION parameter. On our database value for this parameter is set as 900 (15 minutes). So if we run the same query after 15 minutes, we don’t get anything back. Following is the example.

SELECT versions_Starttime, versions_endtime,
versions_xid, CASE
WHEN versions_operation = ‘I’ THEN ‘INSERT’
WHEN versions_operation = ‘U’ THEN ‘UPDATE’
WHEN versions_operation = ‘D’ THEN ‘DELETE’
END AS Operation,
tran_amt
FROM test_ac
VERSIONS BETWEEN TIMESTAMP minvalue AND maxvalue
ORDER BY versions_Startscn;

Share this:

Like this:

LikeLoading...

Related

This entry was posted on July 8, 2008 at 2:59 pm and is filed under Oracle.
You can follow any responses to this entry through the RSS 2.0 feed.
You can leave a response, or trackback from your own site.