Oracle Flashback Transaction Query 10g

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_QUERYWe get following information from FLASHBACK_TRANSACTION_QUERY view1. XID 2. STAR_SCN 3. START_TIMESTAMP 4. COMMIT_SCN 5. UNDO_SQLXID is represent transaction ID,
we can get XID from FLASHBACK VERSION QUERYFor 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 QUERYselect versions_xid from scott.taj versions between timestampto_timestamp ( '2008-05-17 09:18:00','YYYY-MM-DD HH24:MI:SS') andto_timestamp ( '2008-05-17 09:22:00','YYYY-MM-DD HH24:MI:SS');VERSIONS_XID ---------------- 050017007C070000SQL> select logon_user,operation,undo_sql2 from flashback_transaction_query3 where xid = HEXTORAW ('050017007C070000');LOGON_USER OPERATION UNDO_SQL---------- ---------- ----------------------------------------------SCOTT DELETE insert into "SCOTT"."Shahid"("NAME","NO") values ('Shahid','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 Shahid 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 Shahid 12407151 Shahid 3Latest COMMIT is performed on Shahid 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_sqlfrom flashback_transaction_querywhereCOMMIT_TIMESTAMP >= to_timestamp('17-MAY-05 09:18:00','DD-MON-RR HH:MI:SS')and TABLE_NAME = 'Shahid';
UNDO_SQL--------------------------------------------------------------------------------insert into "SCOTT"."Shahid"("NAME","NO") values ('Shahid','4');