打算写一系列的文章介绍11g的新特性和变化。

Oracle11g提供了撤销事务的功能，可以撤销一个已经提交的事务。

这一篇介绍撤销事务与外键约束的关系。

Oracle11新特性——撤销事务（一）：http://yangtingkun.itpub.net/post/468/419695

Oracle11新特性——撤销事务（二）：http://yangtingkun.itpub.net/post/468/443057

上面一篇文章简单介绍了撤销事务的几种选项，这几种选项是对关联事务而言的，对于事务之前的主外键约束，结果又是不同的。

先建立测试环境：

SQL> CREATE TABLE T_PRIMARY (ID NUMBER PRIMARY KEY, NAME VARCHAR2(30));

表已创建。

SQL> CREATE TABLE T_FOREIGN (FID NUMBER, FOREIGN KEY (FID) REFERENCES T_PRIMARY);

表已创建。

SQL> INSERT INTO T_PRIMARY VALUES (1, 'A');

已创建 1 行。

SQL> COMMIT;

提交完成。

SQL> UPDATE T_PRIMARY SET NAME = 'A1';

已更新 1 行。

SQL> INSERT INTO T_PRIMARY VALUES (2, 'B');

已创建 1 行。

SQL> COMMIT;

提交完成。

SQL> INSERT INTO T_FOREIGN VALUES (2);

已创建 1 行。

SQL> INSERT INTO T_PRIMARY VALUES (3, 'C');

已创建 1 行。

SQL> COMMIT;

提交完成。

SQL> CONN SYS/test@172.0.2.61/TEST11G.NETDB AS SYSDBA已连接。
SQL> COL OPERATION FORMAT A10
SQL> COL UNDO_SQL FORMAT A85
SQL> SET LINES 120 PAGES 100
SQL> SELECT XID, OPERATION, UNDO_SQL
2 FROM FLASHBACK_TRANSACTION_QUERY
3 WHERE TABLE_NAME = 'T_PRIMARY';

XID OPERATION UNDO_SQL
---------------- ---------- ------------------------------------------------------------------------
000500590000035B INSERT delete from "YANGTK"."T_PRIMARY" where ROWID = 'AAARuLAAFAAACY9AAB';
000500590000035B UPDATE update "YANGTK"."T_PRIMARY" set "NAME" = 'A' where ROWID = 'AAARuLAAFAAA
0009000500000363 INSERT delete from "YANGTK"."T_PRIMARY" where ROWID = 'AAARuLAAFAAACY9AAC';
000A00680000037C INSERT delete from "YANGTK"."T_PRIMARY" where ROWID = 'AAARuLAAFAAACY9AAA';

下面开始进行撤销测试：

SQL> DECLARE
2 V_XID XID_ARRAY;
3 BEGIN
4 V_XID := SYS.XID_ARRAY('000500590000035B');
5 DBMS_FLASHBACK.TRANSACTION_BACKOUT(1, V_XID);
6 END;
7 /
DECLARE
*第 1 行出现错误:
ORA-55511: 闪回事务处理在执行还原 SQL 时出错
ORA-02292: 违反完整约束条件 (ORA-02292: 违反完整约束条件 (YANGTK.SYS_C0011048) - 已找到子记录
.) - 已找到子记录
ORA-06512: 在 "SYS.DBMS_FLASHBACK", line 37
ORA-06512: 在 "SYS.DBMS_FLASHBACK", line 70
ORA-06512: 在 line 5

由于要撤销的事务中包括对主表记录的插入，而这条记录被子表引用，因此撤销肯定会出现错误。尝试使用CASCADE方式进行撤销：

SQL> DECLARE
2 V_XID XID_ARRAY;
3 BEGIN
4 V_XID := SYS.XID_ARRAY('000500590000035B');
5 DBMS_FLASHBACK.TRANSACTION_BACKOUT(1, V_XID, DBMS_FLASHBACK.CASCADE);
6 END;
7 /
DECLARE
*第 1 行出现错误:
ORA-55511: 闪回事务处理在执行还原 SQL 时出错
ORA-02292: 违反完整约束条件 (ORA-02292: 违反完整约束条件 (YANGTK.SYS_C0011048) - 已找到子记录
.) - 已找到子记录
ORA-06512: 在 "SYS.DBMS_FLASHBACK", line 37
ORA-06512: 在 "SYS.DBMS_FLASHBACK", line 70
ORA-06512: 在 line 5

CASCADE方式只对关联事务有效，而对这种主外键关系无效，因此，如果撤销事务操作会违反主外键约束，那么即使使用CASCADE方式也没有作用。

同样的，另外两种方式NONCONFLICT_ONLY和NOCASCADE_FORCE也没有作用：

SQL> DECLARE
2 V_XID XID_ARRAY;
3 BEGIN
4 V_XID := SYS.XID_ARRAY('000500590000035B');
5 DBMS_FLASHBACK.TRANSACTION_BACKOUT(1, V_XID, DBMS_FLASHBACK.NONCONFLICT_ONLY);
6 END;
7 /
DECLARE
*第 1 行出现错误:
ORA-55511: 闪回事务处理在执行还原 SQL 时出错
ORA-02292: 违反完整约束条件 (ORA-02292: 违反完整约束条件 (YANGTK.SYS_C0011048) - 已找到子记录
.) - 已找到子记录
ORA-06512: 在 "SYS.DBMS_FLASHBACK", line 37
ORA-06512: 在 "SYS.DBMS_FLASHBACK", line 70
ORA-06512: 在 line 5

SQL> DECLARE
2 V_XID XID_ARRAY;
3 BEGIN
4 V_XID := SYS.XID_ARRAY('000500590000035B');
5 DBMS_FLASHBACK.TRANSACTION_BACKOUT(1, V_XID, DBMS_FLASHBACK.NOCASCADE_FORCE);
6 END;
7 /
DECLARE
*第 1 行出现错误:
ORA-55511: 闪回事务处理在执行还原 SQL 时出错
ORA-02292: 违反完整约束条件 (ORA-02292: 违反完整约束条件 (YANGTK.SYS_C0011048) - 已找到子记录
.) - 已找到子记录
ORA-06512: 在 "SYS.DBMS_FLASHBACK", line 37
ORA-06512: 在 "SYS.DBMS_FLASHBACK", line 70
ORA-06512: 在 line 5

唯一的方法是包括参考主键信息的外键事务一起进行撤销：

SQL> DECLARE
2 V_XID XID_ARRAY;
3 BEGIN
4 V_XID := SYS.XID_ARRAY('0009000500000363', '000500590000035B');
5 DBMS_FLASHBACK.TRANSACTION_BACKOUT(2, V_XID);
6 END;
7 /

PL/SQL 过程已成功完成。

SQL> SELECT * FROM YANGTK.T_PRIMARY;

ID NAME
---------- ------------------------------
1 A

感觉这一点上撤销事务的实现有点问题，主外键约束事务的撤销应该可以仿效关联事务的方式实现。否则撤销事务在实际工作中就会很难使用。