Oracle Flashback Transaction Query

Through Flashback Transaction query we can obtain transaction information, including SQL code.

Flashback Transaction query queries the static data dictionary view FLASHBACK_TRANSACTION_QUERY

We get following information from FLASHBACK_TRANSACTION_QUERY view
1. XID
2. STAR_SCN
3. START_TIMESTAMP
4. COMMIT_SCN
5. COMMIT_TIMESTAMP
6. LOGON_USER
7. UNDO_CHANGE#
8. OPERATION
9. TABLE_NAME
10. TABLE_OWNER
11. ROW_ID
12. UNDO_SQL

XID is represent transaction ID, we can get XID from FLASHBACK VERSION QUERY

For example:
Scott user by mistake delete one row from TAJ table and now system user want to know exact TRANSACTION (sql code) for deleted row.
We can query in FLASHBACK_TRANSACTION_QUERY view and get exact transaction (sql code)

---First check what TRASACTION_ID is for deleted row through FLASHBACK VERSION QUERY

select versions_xid
from scott.taj
versions between timestamp
to_timestamp ( '2008-05-17 09:18:00','YYYY-MM-DD HH24:MI:SS') and
to_timestamp ( '2008-05-17 09:22:00','YYYY-MM-DD HH24:MI:SS');
VERSIONS_XID
----------------
050017007C070000

SQL> select logon_user,operation,undo_sql
2 from flashback_transaction_query
3 where xid = HEXTORAW ('050017007C070000');

LOGON_USER OPERATION UNDO_SQL
---------- ---------- -------------------------------------------------------
SCOTT DELETE insert into "SCOTT"."TAJ"("NAME","NO") values ('TAJ','4');

So above is DELETE operation performed by SCOTT user and above UNDO_SQL is generated.

Suppose we don’t know about exact timing then we can use SCN (system change number) when last commit is performed.

We can get SCN number through ORA_ROWSCN pseudo column, ORA_ROWSCN is represents SCN of the most recent change to given row, that is latest COMMIT operation for the row.
Suppose with above example if we don’t know exact timing then row deleted in TAJ table. So we can get exact timing from ORA_ROWSCN pseudo column.

SQL> select ORA_ROWSCN, taj.* from scott.taj;
ORA_ROWSCN NAME NO
---------- -------------------- ----------
2407151 TAJ 1
2407151 TAJ 3

Latest COMMIT is performed on TAJ table is 2407151.
Now change SCN to TIMESTAMP to get exact timing when last commit was performed.

SQL> select scn_to_timestamp('2407151') from dual;
SCN_TO_TIMESTAMP('2407151')
---------------------------------------------------------------------------
17-MAY-08 09.18.20.000000000 AM

Last COMMIT is performed 17-may-08 09:18 AM.

select undo_sql
from flashback_transaction_query
where
COMMIT_TIMESTAMP >= to_timestamp('17-MAY-05 09:18:00','DD-MON-RR HH:MI:SS')
and TABLE_NAME = 'TAJ';

UNDO_SQL
--------------------------------------------------------------------------------
insert into "SCOTT"."TAJ"("NAME","NO") values ('TAJ','4');