闪回事务查询Flashback Transaction Query(3)

060016002F060000    1940047    1940079 AAAV4EAABAAARfpAAK  DELETE                          insert into "SYS"."TEST"("EMPNO","SAL") values ('7876','1100');

060016002F060000    1940047    1940079 AAAV4EAABAAARfpAAJ  DELETE                          insert into "SYS"."TEST"("EMPNO","SAL") values ('7844','1500');

060016002F060000    1940047    1940079 AAAV4EAABAAARfpAAI  DELETE                          insert into "SYS"."TEST"("EMPNO","SAL") values ('7839','5000');

060016002F060000    1940047    1940079 AAAV4EAABAAARfpAAH  DELETE                          insert into "SYS"."TEST"("EMPNO","SAL") values ('7788','3000');

060016002F060000    1940047    1940079 AAAV4EAABAAARfpAAG  DELETE                          insert into "SYS"."TEST"("EMPNO","SAL") values ('7782','2450');

060016002F060000    1940047    1940079 AAAV4EAABAAARfpAAF  DELETE                          insert into "SYS"."TEST"("EMPNO","SAL") values ('7698','2850');

060016002F060000    1940047    1940079 AAAV4EAABAAARfpAAE  DELETE                          insert into "SYS"."TEST"("EMPNO","SAL") values ('7654','1250');

060016002F060000    1940047    1940079 AAAV4EAABAAARfpAAD  DELETE                          insert into "SYS"."TEST"("EMPNO","SAL") values ('7566','2975');

060016002F060000    1940047    1940079 AAAV4EAABAAARfpAAC  DELETE                          insert into "SYS"."TEST"("EMPNO","SAL") values ('7521','1250');

060016002F060000    1940047    1940079 AAAV4EAABAAARfpAAB  DELETE                          insert into "SYS"."TEST"("EMPNO","SAL") values ('7499','1600');

060016002F060000    1940047    1940079 AAAV4EAABAAARfpAAA  DELETE                          insert into "SYS"."TEST"("EMPNO","SAL") values ('7369','800');

07000500D6050000    1940035    1940037 AAAV4EAABAAARfpAAA  UPDATE                          update "SYS"."TEST" set "SAL" = '100' where ROWID = 'AAAV4EAABAAARfpAAA';

0900130035060000    1939850    1939857 AAAV4EAABAAARfpAAA  UPDATE                          update "SYS"."TEST" set "SAL" = '800' where ROWID = 'AAAV4EAABAAARfpAAA';

16 rows selected

3、xid检索

最后我们聊聊查询flashback_transaction_query视图使用XID事务唯一标记特点。视图中xid类型是一个RAW类型，表现出来通常是一个字符串。

在实际中，我们常常发现使用字符串标记进行检索的时候速度比较慢。

SQL> select xid, start_scn, commit_scn, row_id, operation,undo_sql from flashback_transaction_query where xid='060016002F060000';

XID              START_SCN COMMIT_SCN ROW_ID              OPERATION                        UNDO_SQL

---------------- ---------- ---------- ------------------- -------------------------------- --------------------------------------------------------------------------------

060016002F060000    1940047    1940079 AAAV4EAABAAARfpAAN  DELETE                          insert into "SYS"."TEST"("EMPNO","SAL") values ('7934','1300');

（篇幅原因，有省略……）

15 rows selected

Executed in 10.686 seconds

在官方推荐的查询方式中，建议使用hextoraw函数对字符串进行处理一下。

SQL> select xid, start_scn, commit_scn, row_id, operation,undo_sql from flashback_transaction_query where xid=hextoraw('060016002F060000');

XID              START_SCN COMMIT_SCN ROW_ID              OPERATION                        UNDO_SQL

---------------- ---------- ---------- ------------------- -------------------------------- --------------------------------------------------------------------------------