VMCD.ORG » 10g Flashback Version Query & Flashback Transaction Query

Flashback Version Query:
—————————-

Using undo data stored in the database, one can now view the changes to one or more rows along with all the metadata of the changes. Flashback version query allows the versions of a specific row to be tracked during a window of time using the VERSIONS BETWEEN clauses:

CREATE TABLE flashback_version_query (id NUMBER(10), description VARCHAR2(50));

INSERT INTO flashback_version_query (id, description) VALUES (1, ‘Kunal’);

COMMIT;

SELECT current_scn, TO_CHAR(SYSTIMESTAMP, ‘YYYY-MM-DD HH24:MI:SS’) FROM v$database;

CURRENT_SCN TO_CHAR(SYSTIMESTAMP,’YYYY-MM-
———– ——————————
548929 2011-11-26 12:22:59

UPDATE flashback_version_query SET description = ‘John’ WHERE id = 1;
COMMIT;
UPDATE flashback_version_query SET description = ‘Mathew’ WHERE id = 1;
COMMIT;

SELECT current_scn, TO_CHAR(SYSTIMESTAMP, ‘YYYY-MM-DD HH24:MI:SS’) FROM v$database;

CURRENT_SCN TO_CHAR(SYSTIMESTAMP,’YYYY-MM-
———– ——————————
548947 2011-11-26 12:23:39

SELECT versions_startscn, versions_starttime,
versions_endscn, versions_endtime,
versions_xid, versions_operation,
description
FROM flashback_version_query
VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP(‘2011-11-26 12:22:59’, ‘YYYY-MM-DD HH24:MI:SS’)
AND TO_TIMESTAMP(‘2011-11-26 12:23:39’, ‘YYYY-MM-DD HH24:MI:SS’)
WHERE id = 1;

VERSIONS_STARTSCN VERSIONS_STARTTIME VERSIONS_ENDSCN VERSIONS_ENDTIME VERSIONS_XID VERSIONS_OPERATION DESCRIPTION
1 548943 26-11月-11 12.23.28 下午 0300120014010000 U Mathew
2 548937 26-11月-11 12.23.16 下午 548943 26-11月-11 12.23.28 下午 0900020048010000 U John
3 548937 26-11月-11 12.23.16 下午 Kunal

Flashback transaction query can be used to get extra information about the transactions listed by flashback version queries.
The VERSIONS_XID column values from a flashback version query can be used to query the FLASHBACK_TRANSACTION_QUERY view like:

SELECT xid, operation, start_scn,commit_scn, logon_user, undo_sql
FROM flashback_transaction_query
WHERE xid = HEXTORAW(‘0300120014010000’)

XID OPERATION START_SCN COMMIT_SCN LOGON_USER UNDO_SQL
1 0300120014010000 UPDATE 548937 548943 LIU update “LIU”.”FLASHBACK_VERSION_QUERY” set “DESCRIPTION” = ‘John’ where ROWID = ‘AAAM0sAAEAAAAGGAAA’;
2 0300120014010000 BEGIN 548937 548943 LIU