闪回事务处理查询

1)  11g 闪回事务处理查询必须要启用最小补充日志

SYS@ORA11GR2>alter database add supplemental log data;

Database altered.

SYS@ORA11GR2>select supplemental_log_data_min from v$database;

SUPPLEME

--------

YES

===========================================================================================

注 默认情况下最小补充日志是未开启的，关闭命令如下：
SYS@ORA11GR2>select SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEME
--------
YES
SYS@ORA11GR2>alter database drop supplemental log data;
Database altered.
SYS@ORA11GR2>select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
SUPPLEME
--------
NO
=======================================================

2) 创建测试表，并对其进行 DML 操作

SYS@ORA11GR2>create table t(id number(2),name varchar2(10)) tablespace users;

Table created.

SYS@ORA11GR2>insert into t values (1,'zhangsan');

1 row created.

SYS@ORA11GR2>update t set name='sss' where id=1;

1 row updated.

SYS@ORA11GR2>commit;

Commit complete.

3) 执行闪回版本查询，得到事务 ID

SYS@ORA11GR2>select versions_xid,versions_startscn,id,name from t versions between timestamp minvalue and maxvalue;

VERSIONS_XID     VERSIONS_STARTSCN         ID NAME

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

0B001700E1000000           1761763          1 sss

4) 通过事务 ID 得到相关 SQL
事务'0B001700E1000000'做了两个操作：插入数据、更新数据，闪回事务会逆向的返回这个事务
的操作，通过这组 SQL 可修改会事务修改前的样子：

SYS@ORA11GR2>select undo_sql from flashback_transaction_query where xid='0B001700E1000000';

UNDO_SQL

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

update "SYS"."T" set "NAME" = 'zhangsan' where ROWID = 'AAAV73AAEAAAAJEAAA';

delete from "SYS"."T" where ROWID = 'AAAV73AAEAAAAJEAAA';

通过上述 SQL 可修改会事务修改前的样子，操作如下：
SQL> update "SCOTT"."T" set "NAME" = 'zhangsan' where ROWID = 'AAAWD/AAEAAAArUAAA';

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from t;

        ID NAME
---------- ----------
         1 zhangsan

完成！！！！！！！！！！！！！！！！！！！