闪回事务处理

1.闪回事务处理功能的前提

SQL> show user;

USER is "SYS"

SQL> alter database add supplemental log data;

 

Database altered.

 

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

 

Database altered.

 

SQL> grant execute on dbms_flashback to scott;

 

Grant succeeded.

 

SQL> grant select any transaction to scott;

 

Grant succeeded.

 

2.创建一张实验表

SQL> create table t (id number,name varchar2(10));

 

Table created.

 

第一个事务，插入两行，提交

 

SQL> insert into t values(1,'a');

 

1 row created.

 

SQL> insert into t values(2,'b');

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

 

第二事务，插入5行，提交

 

SQL> insert into t values(3,'c');

 

1 row created.

 

SQL> insert into t values(3,'c');

 

1 row created.

 

SQL> insert into t values(3,'c');

 

1 row created.

 

SQL> insert into t values(3,'c');

 

1 row created.

 

SQL> insert into t values(3,'c');

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

执行一个闪回版本查询

SQL>select versions_starttime,versions_endtime,versions_startscn,

versions_endscn,versions_xid,versions_operation from scott.t1

versions between scn minvalue and maxvalue order by 1;

 

 

 

VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID V

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

30-MAY-16 02.44.39 PM 30-MAY-16 02.55.33 PM 1040502 1042027 02000500B2020000 I

30-MAY-16 02.44.39 PM 30-MAY-16 02.55.33 PM 1040502 1042027 02000500B2020000 I

30-MAY-16 02.45.15 PM 1040522 01001F00FC010000 I

30-MAY-16 02.45.15 PM 1040522 01001F00FC010000 I

30-MAY-16 02.45.15 PM 1040522 01001F00FC010000 I

30-MAY-16 02.45.15 PM 1040522 01001F00FC010000 I

30-MAY-16 02.45.15 PM 1040522 01001F00FC010000 I

30-MAY-16 02.55.33 PM 1042027 01002100FC010000 D

30-MAY-16 02.55.33 PM 1042027 01002100FC010000 D

 

 

 

3.执行闪回事务查询

闪回事务查询，可以看到，一共两个事务，一个事务包含两条DML，一个事务包含五条DML

通过UNDO_SQL字段给出反SQL

我们可以手动的取消某一个事务

但是，当事务比较复杂，事务中包含DML较多的情况下

手动去执行闪回事务处理，将变得困难，且容易出错

 

数据库提供了dbms_flashback.transaction_backout函数

帮助我们自动的，去执行同一个事务下，所有反SQL

能够保障事务的完整性

SQL> select xid,operation,undo_sql

2 from flashback_transaction_query

3 where table_name='T';

 

XID OPERATION UNDO_SQL

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

01000200ED010000 INSERT delete from "SCOTT"."T" where ROWID = 'AAASO5AAEAA

AAIUAAB';

 

01000200ED010000 INSERT delete from "SCOTT"."T" where ROWID = 'AAASO5AAEAA

AAIUAAA';

 

06000B00AF020000 INSERT delete from "SCOTT"."T" where ROWID = 'AAASO5AAEAA

AAIUAAG';

 

06000B00AF020000 INSERT delete from "SCOTT"."T" where ROWID = 'AAASO5AAEAA

AAIUAAF';

 

06000B00AF020000 INSERT delete from "SCOTT"."T" where ROWID = 'AAASO5AAEAA

AAIUAAE';

 

06000B00AF020000 INSERT delete from "SCOTT"."T" where ROWID = 'AAASO5AAEAA

AAIUAAD';

 

06000B00AF020000 INSERT delete from "SCOTT"."T" where ROWID = 'AAASO5AAEAA

AAIUAAC';

 

7 rows selected.

 

 

4.执行闪回事务处理

闪回事务处理需要SYS用户去执行

SQL> show user;

USER is "SYS"

SQL> declare

2 v_xid sys.xid_array;

3 begin

4 v_xid := sys.xid_array('01000200ED010000');

5 dbms_flashback.transaction_backout(1,v_xid);

6 end;

7 /

 

PL/SQL procedure successfully completed.

 

在当前会话，查询闪回之后的结果，因为此时该事务未提交，所以别的会话看不到闪回后的结果

在确认，闪回达到预期之后，提交

SQL> select * from scott.t;

 

ID NAME

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

3 c

3 c

3 c

3 c

3 c

 

SQL> commit;

 

Commit complete.

 

 

回到SCOTT进行确认

 

SQL> select * from t;

 

ID NAME

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

3 c

3 c

3 c

3 c

3 c