Flashback Transaction History

Oracle Tips by Burleson

Oracle 10g Flashback Transaction History

The Flashback Transaction History feature provides a way to view changes made to the database at the transaction level. It allows you to diagnose problems in your database and perform analysis and audit transactions. You can use this feature in conjunction with the Flash Row History feature to roll back the changes made by a transaction. You can also use this feature to audit user and application transactions. The Flashback Transaction History provides a faster way to undo a transaction than LogMiner.

You can retrieve the transaction history from dba_transaction_query view:

SQL> desc dba_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)

SQL> select versions_xid, name, salary
2 from emp
3 versions between scn minvalue and maxvalue;

VERSIONS_XID     NAME           SALARY
---------------- ---------- ----------
0003000E00000FE2 DANIEL           3000
                 DANIEL           2000

SQL> select *
2 from dba_transaction_query
3 where xid = '0003000E00000FE2';