通过Flashback Transaction Query进行事务撤销

具备了Flashback Version Query查询的基础，就可以进行基于Flashback Version Query的恢复，这就是Flashback Transaction Query。

Flashback Transaction Query 是一个诊断恢复工具，可以用于在事务级识别数据库的变更，与Flashback Versions Query类似， Flashback Transaction Query允许我们获取两个特定时间点之间的所有变更，更进一步的，Flashback Transaction Query 允许我们对于表进行基于事务的恢复。

Flashback Transaction Query可以从FLASHBACK_TRANSACTION_QUERY中获得指定事务的历史信息以及Undo_SQL，通过这个UNDO_SQL就可以恢复特定的事务。

使用Flashback Transaction Query需要满足以下两个条件：

• 确保数据库的兼容性设置在 10.0 之上
• 需要启用Supplemental logging (ALTER DATABASE ADD SUPPLEMENTAL LOG DATA)

使用这一特性需要用户具备 SELECT ANY TRANSACTION 的系统权限，并且用户需要具有基于特定表的 FLASHBACK 权限（或者具有FLASHBACK ANY TABLE 系统权限）。

更多资料可咨询“恩墨学院”公主号，Flashback Transaction Query需要用到FLASHBACK_TRANSACTION_QUERY视图，先看一下视图：

SQL> desc FLASHBACK_TRANSACTION_QUERY;
Name             Type           Nullable Default Comments                
---------------- -------------- -------- ------- -------------------------- 
XID              RAW(8)          Y       Transaction identifier   
START_SCN        NUMBER          Y       Transaction start SCN    
START_TIMESTAMP  DATE            Y       Transaction start timestamp   
COMMIT_SCN       NUMBER          Y       Transaction commit SCN  
COMMIT_TIMESTAMP DATE            Y       Transaction commit timestamp  
LOGON_USER       VARCHAR2(30)    Y       Logon user for transaction   
UNDO_CHANGE#     NUMBER          Y       1-based undo change number  
OPERATION        VARCHAR2(32)    Y       forward operation for this undo  
TABLE_NAME       VARCHAR2(256)   Y       table name to which this undo applies     
TABLE_OWNER      VARCHAR2(32)    Y       owner of table to which this undo applies
ROW_ID           VARCHAR2(19)    Y       rowid to which this undo applies          
UNDO_SQL         VARCHAR2(4000)  Y       SQL corresponding to this undo  

该视图的定义为：

select xid, start_scn, start_timestamp,
          decode(commit_scn, 0, commit_scn, 281474976710655, NULL, commit_scn)
          commit_scn, commit_timestamp,
          logon_user, undo_change#, operation, table_name, table_owner,
          row_id, undo_sql
from sys.x$ktuqqry

根据上一节的介绍，可以通过Flashback Version Query获得XID，此处我们来尝试恢复XID=000A000B000000F1的事务。

 

SYS AS SYSDBA >SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY
  2  WHERE XID = hextoraw('000A000B000000F1');
UNDO_SQL
--------------------------------------------------------------------------------
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('DBSNMP','22');
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('WMSYS','23');
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('OPERATOR','31');
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('TRANS','27');
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('DIP','19');
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('SCOTT','29');
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('TEST','25');

8 rows selected.

通过UNDO语句就可以撤销该事务。