Using flashback versions query, one can look into the historical changes that were made to the tables. Before Oracle 10g, it was not possible to view series of changes made to the table in the past unless one is doing auditing. 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 for this post…
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 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. For performance suggestions and detailed description of flashback query, please refer to Oracle 10g Application Developers’ Guide.
Systems Engineering and RDBMS 