1、应用Flashback Version Query查询记修改版本

SQL> select dbms_flashback.get_system_change_number from dual; --查询当前SCN值GET_SYSTEM_CHANGE_NUMBER------------------------1574142SQL> update flash_tab set id=id+100 where id>15;已更新5行。SQL> commit;提交完成。SQL> delete flash_tab where id<15;已删除14行。SQL> rollback;回退已完成。SQL> select * from flash_tab;ID VL---------- --10 I11 J12 K13 L14 M15 N116 O117 P118 Q119 R120 S1 /2 A3 B4 C5 D6 E7 F8 G9 H已选择20行。SQL> delete flash_tab where id<5;已删除4行。SQL> commit;提交完成。SQL> select * from flash_tab;ID VL---------- --10 I11 J12 K13 L14 M15 N116 O117 P118 Q119 R120 S5 D6 E7 F8 G9 H已选择16行。SQL> insert into flash_tab values(201,'A1');已创建 1 行。SQL> commit;提交完成。SQL> insert into flash_tab values(202,'B1');已创建 1 行。SQL> COMMIT;提交完成。SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;GET_SYSTEM_CHANGE_NUMBER------------------------1574208

执行Version Query查询flash_tab表中记录：

SQL> select id,vl,versions_startscn,versions_endscn,versions_operation,versions_xid from flash_tab versions between scn 1574142 and 1574208;ID VL VERSIONS_STARTSCN VERSIONS_ENDSCN V VERSIONS_XID---------- -- ----------------- --------------- - ----------------202 B1 1574198 I 0900170004040000201 A1 1574192 I 03001200340400004 C 1574180 D 050015001B0400003 B 1574180 D 050015001B0400002 A 1574180 D 050015001B0400001 / 1574180 D 050015001B040000120 S 1574157 U 0600180005040000119 R 1574157 U 0600180005040000118 Q 1574157 U 0600180005040000117 P 1574157 U 0600180005040000116 O 1574157 U 060018000504000010 I11 J12 K13 L14 M15 N16 O 157415717 P 157415718 Q 157415719 R 157415720 S 15741571 / 15741802 A 15741803 B 15741804 C 15741805 D6 E7 F8 G9 H已选择31行。

2.使用Flashback Transaction Query查询事务信息

用户在执行闪回事务查询之前，必须为用户授予适当的DBMS_FLASHBACK包上的权限。grant execute on dbms_flashback to scott;grant select any transaction to scott;--------开启flashback:SQL> shutdown immediate;SQL> startup mount;SQL> alter database flashback on;SQL> alter database open;SQL> conn / as sysdba;已连接。SQL> alter database add supplemental log data;数据库已更改。-------------------------------------------------------启用重做日志流,否则查询Flashback_transaction_query的undo_sql列为空，并且OPERATION列为UNKNOWN， SQL> alter database add supplemental log data;Database altered.

测试案例：

SQL> conn scott/tiger;已连接。-------------------SQL> select current_scn from v$database;CURRENT_SCN-----------1586922SQL> select * from flash_tab;ID VL---------- --1 a2 tsSQL> update flash_tab set vl='b' where id=1;已更新 1 行。SQL> commit;提交完成。SQL> insert into flash_tab values(3,'c');已创建 1 行。SQL> commit;提交完成。SQL> select current_scn from v$database;CURRENT_SCN-----------1586956======================SQL> select xid,commit_scn,commit_timestamp,operation,undo_sql2 from flashback_transaction_query q where q.xid in( select versions_xid from scott.flash_tab versions between scn 1586922 and 1586956);XID COMMIT_SCN COMMIT_TIMESTAMP OPERATION UNDO_SQL---------------- ---------- ---------------- -------------------------------- --------------------------------------------------------------------------------02001200FE030000 1586949 2015/1/10 17:03: INSERT delete from "SCOTT"."FLASH_TAB" where ROWID = 'AAASZaAAEAAAAJ2AAB';02001200FE030000 1586949 2015/1/10 17:03: BEGIN 06001C000A040000 1586940 2015/1/10 17:03: UPDATE update "SCOTT"."FLASH_TAB" set "VL" = 'a' where ROWID = 'AAASZaAAEAAAAJ0AAC';06001C000A040000 1586940 2015/1/10 17:03: BEGIN