Systems Engineering and RDBMS

Flashback Versions Query

Posted by decipherinfosys on July 8, 2008

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.

CREATE TABLE TEST_AC
(
TEST_AC_ID NUMBER(9) NOT NULL,
TRAN_DATE DATE,
TRAN_AMT NUMBER(9,2) NOT NULL,
CONSTRAINT PK_TEST_AC PRIMARY KEY(TEST_AC_ID)
)
— TABLESPACE Clause
/

Populate it with some data so that we can test our flashback query.

INSERT INTO TEST_AC(test_ac_id,tran_date,tran_amt) VALUES(101,sysdate,5000);
COMMIT;
UPDATE TEST_AC SET TRAN_AMT = TRAN_AMT + 2000 WHERE TEST_AC_ID = 101;
COMMIT;
UPDATE TEST_AC SET TRAN_AMT = TRAN_AMT – 5000 WHERE TEST_AC_ID = 101;
COMMIT;
UPDATE TEST_AC SET TRAN_AMT = TRAN_AMT + 200 WHERE TEST_AC_ID = 101;
COMMIT;

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;

Output is shown below.

VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID     OPERAT   TRAN_AMT
----------------- --------------- ---------------- ------ ----------
          4168035         4168037 07001500F6070000 INSERT       5000
          4168037         4168039 09002C00420A0000 UPDATE       7000
          4168039         4168042 04002600C8070000 UPDATE       2000
          4168042                 0800030075090000 UPDATE       2200

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.

VERSIONS_STARTTIME        VERSIONS_ENDTIME      VERSIONS_XID     OPERAT   TRAN_AMT
------------------------- --------------------- ---------------- ------ ----------
22-FEB-07 10.52.02 AM     22-FEB-07 10.52.02 AM 07001500F6070000 INSERT       5000
22-FEB-07 10.52.02 AM     22-FEB-07 10.52.02 AM 09002C00420A0000 UPDATE       7000
22-FEB-07 10.52.02 AM     22-FEB-07 10.52.08 AM 04002600C8070000 UPDATE       2000
22-FEB-07 10.52.08 AM                           0800030075090000 UPDATE       2200

Flashback Transaction Query

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.

SQL>SELECT operation, logon_user, undo_Sql
FROM flashback_transaction_query where xid = ‘09002C00420A0000’;

OPERATION  LOGON_USER UNDO_SQL
---------- ---------- ------------------------------------------------------------
UPDATE     DECIPHER   update "DECIPHER"."TEST_AC" set "TRAN_AMT" = '7000' where
                      ROWID = 'AAANtqAAEAAAAHGAAA';

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;

VERSIONS_STARTTIME        VERSIONS_ENDTIME      VERSIONS_XID     OPERATION    TRAN_AMT
------------------------- --------------------- ---------------- ---------- ----------
                                                                            2200

Also as per Oracle documentation, specify the RETENTION GUARANTEE clause for the undo tablespace to ensure that unexpired undo is not discarded.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: