【赵渝强老师】Oracle的闪回事务查询

Oracle数据库的闪回事务查询（Flashback Transaction Query）实际上闪回版本查询的一个扩充，通过它可以审计某个事务甚至撤销一个已经提交的事务。

视频讲解如下：

https://www.bilibili.com/video/BV1L5UKYBE4c/?aid=113474344656...

一、 闪回事务查询简介

闪回事务处理查询是一种诊断工具，可以用来查看在事务处理级对数据库所做的更改。通过这样的方式，可以诊断数据库中的问题并对事务处理执行分析和审计，甚至撤销一个已经提交了的事务。

闪回事务查询的核心是使用flashback_transaction_query视图来确定所有必要的SQL 语句。这些语句可以用来还原特定事务处理或特定时间段内所做的修改。通过下面的语句可以查看flashback_transaction_query视图的结构。

SQL> desc flashback_transaction_query

# 输出的信息如下：
 Name                        Null?    Type
 ------------------------ -------- --------
 XID                                RAW(8)
 START_SCN                            NUMBER
 START_TIMESTAMP                    DATE
 COMMIT_SCN                            NUMBER
 COMMIT_TIMESTAMP                    DATE
 LOGON_USER                            VARCHAR2(128)
 UNDO_CHANGE#                        NUMBER
 OPERATION                            VARCHAR2(32)
 TABLE_NAME                            VARCHAR2(256)
 TABLE_OWNER                        VARCHAR2(386)
 ROW_ID                                VARCHAR2(19)
 UNDO_SQL                            VARCHAR2(4000)

二、 【实战】在事务中使用闪回事务查询

在了解到了什么是Oracle数据库的闪回事务查询后，下面将通过一个具体是示例来演示如何使用闪回事务查询撤销一个已经提交了的事务。
（1）使用管理员登录数据库，并授权c##scott用户执行事务查询的权限。

SQL> conn / as sysdba
SQL> grant select any transaction to c##scott;

# 执行事务查询查看flashback_transaction_query视图，
# 需要select any transaction的权限。

（2）开启UNDO数据的增强信息。

SQL> alter database add supplemental log data;
SQL> alter database add supplemental log data (primary key) columns;

（3）切换到c##scott用户，并创建一张新表flashback4。

SQL> conn c##scott/tiger
SQL> create table flashback4(tid number, tname varchar2(20));

（4）使用c##scott用户执行第一个事务。

SQL> insert into flashback4 values(1,'Tom');
SQL> insert into flashback4 values(2,'Mary');
SQL> insert into flashback4 values(3,'Mike');
SQL> commit;

（5）使用c##scott用户执行第二个事务。

SQL> update flashback4 set tname='Mary123' where tid=2;
SQL> delete from flashback4 where tid=1;
SQL> commit;

# 当第二个事务执行完成后，如何撤销第二个事务呢？
# 由于该事务已经提交，因此不可能再通过执行rollback语句来撤销了。
# 但是可以通过闪回事务查询来获取撤销事务的SQL语句，最终达到撤销事务的目的。

（6）通过使用闪回版本查询，获取表flashback4上的事务信息。

SQL>select tid,tname,versions_operation,versions_xid
    from flashback4
    versions between timestamp minvalue and maxvalue
    order by versions_xid;

# 输出的信息如下：
TID            TNAME        V    VERSIONS_XID
------ --------------- --- -----------------
     3         Mike         I     03001A0009030000
     2         Mary         I     03001A0009030000
     1         Tom            I     03001A0009030000
     2         Mary123        U     0700210004030000
     1         Tom            D     0700210004030000

# 这里的VERSIONS_XID表示事务的ID号。
# 从输出的信息中可以看出第二个事务的ID为0700210004030000。

（7）查询视图flashback_transaction_query以获取撤销第二个事务的SQL语句。

SQL>select undo_sql 
    from flashback_transaction_query
    where xid='0700210004030000';

# 输出的信息如下：
UNDO_SQL
---------------------------------------------------------------------------------------
insert into "C##SCOTT"."FLASHBACK4"("TID","TNAME") values ('1','Tom');
update "C##SCOTT"."FLASHBACK4" set "TNAME" = 'Mary' where ROWID = 'AAATIvAAHAAAAIWAAB';

（8）执行第（7）步中输出的UNDO_SQL语句。

SQL> insert into "C##SCOTT"."FLASHBACK4"("TID","TNAME") values ('1','Tom');
SQL> update "C##SCOTT"."FLASHBACK4" set "TNAME" = 'Mary' where ROWID = 'AAATIvAAHAAAAIWAAB';

（9）验证第二个事务是否撤销，查询表flashback4的数据。

SQL> select * from flashback4;

# 输出的信息如下：
TID     TNAME
---------- --------------------
     2     Mary
     3     Mike
     1     Tom

# 此时表flashback4便恢复到了第一个事务结束的状态。

（10）提交UNDO_SQL产生的事务。

SQL> commit;