ITPUB论坛 » Oracle新技术/11g » Oracle11新特性——撤销事务
2008-1-11 08:07
yangtingkun
Oracle11新特性——撤销事务
Oracle11g提供了撤销事务的功能，可以撤销一个已经提交的事务。
这一篇简单介绍撤销事务的用法。
Oracle的发起一个事务后，可以选择提交或者回滚。回滚的话，会恢复到这个事务发生之前的状态，而一旦提交，这个事务的所有修改都被保存到数据库中。
从11g开始，Oracle提供了撤销一个已经提交事务的能力。这个功能是通过DBMS_FLASHBACK.TRANSACTION_BACKOUT过程实现的。
首先建立一张测试表：
[PHP]
SQL> CREATE TABLE T (ID NUMBER);
Table created.
SQL> INSERT INTO T VALUES (1);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT XID, TABLE_NAME, OPERATION, UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY WHERE TABLE_NAME = 'T';
XID TABLE_NAME OPERATION UNDO_SQL
---------------- ---------- ---------- ------------------------------------------------------------
0007001100000157 T INSERT delete from YANGTK.T where ROWID = 'AAARNZAAFAAACYxAAA';
.
[/PHP]
找到要撤销的事务后，通过调用DBMS_FLASHBACK.TRANSACTION_BACKOUT过程：
[PHP]
SQL> DECLARE
2 V_XID SYS.XID_ARRAY;
3 BEGIN
4 V_XID := SYS.XID_ARRAY('0002003A00000157');
5 DBMS_FLASHBACK.TRANSACTION_BACKOUT(1, V_XID);
6 END;
7 /
DECLARE
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at SYS.DBMS_FLASHBACK, line 37
ORA-06512: at SYS.DBMS_FLASHBACK, line 70
ORA-06512: at line 5
.
[/PHP]
系统报错没有权限，当前用户拥有DBA角色，仍然权限不足，看来必须要用SYS来执行：
[PHP]
SQL> SELECT * FROM SESSION_ROLES;
ROLE
------------------------------
CONNECT
RESOURCE
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
DATAPUMP_EXP_FULL_DATABASE
DATAPUMP_IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
SCHEDULER_ADMIN
WM_ADMIN_ROLE
JAVA_ADMIN
JAVA_DEPLOY
XDBADMIN
XDB_SET_INVOKER
OLAP_XS_ADMIN
OLAP_DBA
20 rows selected.
.
[/PHP]
使用SYS执行报错如下：
[PHP]
SQL> CONN / AS SYSDBA
Connected.
SQL> DECLARE
2 V_XID XID_ARRAY;
3 BEGIN
4 V_XID := SYS.XID_ARRAY('0002003A00000157');
5 DBMS_FLASHBACK.TRANSACTION_BACKOUT(1, V_XID);
6 END;
7 /
DECLARE
*
ERROR at line 1:
ORA-55510: Mining could not start
ORA-06512: at SYS.DBMS_FLASHBACK, line 37
ORA-06512: at SYS.DBMS_FLASHBACK, line 70
ORA-06512: at line 5
.
[/PHP]
对于这个错误，Oracle的文档上的描述是：
ORA-55510: Mining could not start
Cause: Mining could not start for the following reasons.
1. A logminer session was processing
2. The database was not mounted or not opened for read and write
3. Minimum supplemental logging was not enabled
4. Archiving was not enabled
Action: Fix the mentioned problems and try again. Note that if you enable supplemental logging now, you will not be able to remove a transaction that has committed without supplemental logging.
检查上面这几项，似乎只是违反了第三项：
[PHP]
SQL> SELECT LOG_MODE, SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
LOG_MODE SUPPLEME
------------ --------
ARCHIVELOG NO
.
[/PHP]
下面设置数据库SUPPLEMENTAL LOG DATA：
[PHP]
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.
SQL> DECLARE
2 V_XID XID_ARRAY;
3 BEGIN
4 V_XID := SYS.XID_ARRAY('0002003A00000157');
5 DBMS_FLASHBACK.TRANSACTION_BACKOUT(1, V_XID);
6 END;
7 /
DECLARE
*
ERROR at line 1:
ORA-55515: Mining sees input transaction changes without seeing transaction start
ORA-06512: at "SYS.DBMS_FLASHBACK", line 37
ORA-06512: at "SYS.DBMS_FLASHBACK", line 70
ORA-06512: at line 5
.
[/PHP]
这个错误造成的原因显然是由于INSERT语句发生在设置SUPPLEMENTAL LOG DATA之后，Oracle在LOG中无法找到这个事务。
而且，如果执行与事务发生的间隔超过了UNDO_RETENTION的设置，那么还可能出现下面的错误：
[PHP]
SQL> DECLARE
2 V_XID XID_ARRAY;
3 BEGIN
4 V_XID := SYS.XID_ARRAY('0002003A00000157');
5 DBMS_FLASHBACK.TRANSACTION_BACKOUT(1, V_XID);
6 END;
7 /
DECLARE
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number with name '' too small
ORA-06512: at "SYS.DBMS_FLASHBACK", line 37
ORA-06512: at "SYS.DBMS_FLASHBACK", line 70
ORA-06512: at line 5
.
[/PHP]
从上面的错误已经可以看到，Oracle的撤销事务是通过FLASHBACK查询配合LOGMINER进行的。由于LOGMINER操作只能SYS来进行操作，因此撤销事务也必须使用SYS用户。
了解了使用撤销事务可能碰到的问题，下面看一个真正的例子：
[PHP]
SQL> CONN YANGTK/yangtk
Connected.
SQL> CREATE TABLE T_FLASH_TRANS (ID NUMBER, NAME VARCHAR2(30));
Table created.
SQL> INSERT INTO T_FLASH_TRANS VALUES (1, 'A');
1 row created.
SQL> INSERT INTO T_FLASH_TRANS VALUES (2, 'B');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> INSERT INTO T_FLASH_TRANS SELECT 3 + ROWNUM, 'C' FROM TAB;
4 rows created.
SQL> COMMIT;
Commit complete.
SQL> SELECT XID, TABLE_NAME, OPERATION, UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY
2 WHERE TABLE_NAME = 'T_FLASH_TRANS';
XID TABLE_NAME OPERATIO UNDO_SQL
---------------- ------------- -------- --------------------------------------------------------------------
0002001100000171 T_FLASH_TRANS INSERT delete from YANGTK.T_FLASH_TRANS where ROWID = 'AAARO3AAFAAACY4AAB';
0002001100000171 T_FLASH_TRANS INSERT delete from YANGTK.T_FLASH_TRANS where ROWID = 'AAARO3AAFAAACY4AAA';
000600180000016D T_FLASH_TRANS INSERT delete from YANGTK.T_FLASH_TRANS where ROWID = 'AAARO3AAFAAACY4AAC';
000600180000016D T_FLASH_TRANS INSERT delete from YANGTK.T_FLASH_TRANS where ROWID = 'AAARO3AAFAAACY4AAD';
000600180000016D T_FLASH_TRANS INSERT delete from YANGTK.T_FLASH_TRANS where ROWID = 'AAARO3AAFAAACY4AAE';
000600180000016D T_FLASH_TRANS INSERT delete from YANGTK.T_FLASH_TRANS where ROWID = 'AAARO3AAFAAACY4AAF';
6 rows selected.
.
[/PHP]
对于T_FLASH_TRANS表存在两个事务，分别插入了2条和4条记录，下面利用SYS来撤销掉第一个事务：
[PHP]
SQL> CONN / AS SYSDBA
Connected.
SQL> DECLARE
2 V_XID XID_ARRAY;
3 BEGIN
4 V_XID := SYS.XID_ARRAY('0002001100000171');
5 DBMS_FLASHBACK.TRANSACTION_BACKOUT(1, V_XID);
6 END;
7 /
PL/SQL procedure successfully completed.
.
[/PHP]
下面看看T_FLASH_TRANS表中的数据：
[PHP]
SQL> SELECT * FROM YANGTK.T_FLASH_TRANS;
ID NAME
---------- ------------------------------
4 C
5 C
6 C
7 C
.
[/PHP]
原文出自：[url]http://space.itpub.net/4227/viewspace-69520[/url]
[[i] 本帖最后由 yangtingkun 于 2008-1-11 09:35 编辑 [/i]]
Oracle11g提供了撤销事务的功能，可以撤销一个已经提交的事务。
这一篇简单介绍撤销事务的用法。
Oracle的发起一个事务后，可以选择提交或者回滚。回滚的话，会恢复到这个事务发生之前的状态，而一旦提交，这个事务的所有修改都被保存到数据库中。
从11g开始，Oracle提供了撤销一个已经提交事务的能力。这个功能是通过DBMS_FLASHBACK.TRANSACTION_BACKOUT过程实现的。
首先建立一张测试表：
[PHP]
SQL> CREATE TABLE T (ID NUMBER);
Table created.
SQL> INSERT INTO T VALUES (1);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT XID, TABLE_NAME, OPERATION, UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY WHERE TABLE_NAME = 'T';
XID TABLE_NAME OPERATION UNDO_SQL
---------------- ---------- ---------- ------------------------------------------------------------
0007001100000157 T INSERT delete from YANGTK.T where ROWID = 'AAARNZAAFAAACYxAAA';
.
[/PHP]
找到要撤销的事务后，通过调用DBMS_FLASHBACK.TRANSACTION_BACKOUT过程：
[PHP]
SQL> DECLARE
2 V_XID SYS.XID_ARRAY;
3 BEGIN
4 V_XID := SYS.XID_ARRAY('0002003A00000157');
5 DBMS_FLASHBACK.TRANSACTION_BACKOUT(1, V_XID);
6 END;
7 /
DECLARE
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at SYS.DBMS_FLASHBACK, line 37
ORA-06512: at SYS.DBMS_FLASHBACK, line 70
ORA-06512: at line 5
.
[/PHP]
系统报错没有权限，当前用户拥有DBA角色，仍然权限不足，看来必须要用SYS来执行：
[PHP]
SQL> SELECT * FROM SESSION_ROLES;
ROLE
------------------------------
CONNECT
RESOURCE
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
DATAPUMP_EXP_FULL_DATABASE
DATAPUMP_IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
SCHEDULER_ADMIN
WM_ADMIN_ROLE
JAVA_ADMIN
JAVA_DEPLOY
XDBADMIN
XDB_SET_INVOKER
OLAP_XS_ADMIN
OLAP_DBA
20 rows selected.
.
[/PHP]
使用SYS执行报错如下：
[PHP]
SQL> CONN / AS SYSDBA
Connected.
SQL> DECLARE
2 V_XID XID_ARRAY;
3 BEGIN
4 V_XID := SYS.XID_ARRAY('0002003A00000157');
5 DBMS_FLASHBACK.TRANSACTION_BACKOUT(1, V_XID);
6 END;
7 /
DECLARE
*
ERROR at line 1:
ORA-55510: Mining could not start
ORA-06512: at SYS.DBMS_FLASHBACK, line 37
ORA-06512: at SYS.DBMS_FLASHBACK, line 70
ORA-06512: at line 5
.
[/PHP]
对于这个错误，Oracle的文档上的描述是：
ORA-55510: Mining could not start
Cause: Mining could not start for the following reasons.
1. A logminer session was processing
2. The database was not mounted or not opened for read and write
3. Minimum supplemental logging was not enabled
4. Archiving was not enabled
Action: Fix the mentioned problems and try again. Note that if you enable supplemental logging now, you will not be able to remove a transaction that has committed without supplemental logging.
检查上面这几项，似乎只是违反了第三项：
[PHP]
SQL> SELECT LOG_MODE, SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
LOG_MODE SUPPLEME
------------ --------
ARCHIVELOG NO
.
[/PHP]
下面设置数据库SUPPLEMENTAL LOG DATA：
[PHP]
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.
SQL> DECLARE
2 V_XID XID_ARRAY;
3 BEGIN
4 V_XID := SYS.XID_ARRAY('0002003A00000157');
5 DBMS_FLASHBACK.TRANSACTION_BACKOUT(1, V_XID);
6 END;
7 /
DECLARE
*
ERROR at line 1:
ORA-55515: Mining sees input transaction changes without seeing transaction start
ORA-06512: at "SYS.DBMS_FLASHBACK", line 37
ORA-06512: at "SYS.DBMS_FLASHBACK", line 70
ORA-06512: at line 5
.
[/PHP]
这个错误造成的原因显然是由于INSERT语句发生在设置SUPPLEMENTAL LOG DATA之后，Oracle在LOG中无法找到这个事务。
而且，如果执行与事务发生的间隔超过了UNDO_RETENTION的设置，那么还可能出现下面的错误：
[PHP]
SQL> DECLARE
2 V_XID XID_ARRAY;
3 BEGIN
4 V_XID := SYS.XID_ARRAY('0002003A00000157');
5 DBMS_FLASHBACK.TRANSACTION_BACKOUT(1, V_XID);
6 END;
7 /
DECLARE
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number with name '' too small
ORA-06512: at "SYS.DBMS_FLASHBACK", line 37
ORA-06512: at "SYS.DBMS_FLASHBACK", line 70
ORA-06512: at line 5
.
[/PHP]
从上面的错误已经可以看到，Oracle的撤销事务是通过FLASHBACK查询配合LOGMINER进行的。由于LOGMINER操作只能SYS来进行操作，因此撤销事务也必须使用SYS用户。
了解了使用撤销事务可能碰到的问题，下面看一个真正的例子：
[PHP]
SQL> CONN YANGTK/yangtk
Connected.
SQL> CREATE TABLE T_FLASH_TRANS (ID NUMBER, NAME VARCHAR2(30));
Table created.
SQL> INSERT INTO T_FLASH_TRANS VALUES (1, 'A');
1 row created.
SQL> INSERT INTO T_FLASH_TRANS VALUES (2, 'B');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> INSERT INTO T_FLASH_TRANS SELECT 3 + ROWNUM, 'C' FROM TAB;
4 rows created.
SQL> COMMIT;
Commit complete.
SQL> SELECT XID, TABLE_NAME, OPERATION, UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY
2 WHERE TABLE_NAME = 'T_FLASH_TRANS';
XID TABLE_NAME OPERATIO UNDO_SQL
---------------- ------------- -------- --------------------------------------------------------------------
0002001100000171 T_FLASH_TRANS INSERT delete from YANGTK.T_FLASH_TRANS where ROWID = 'AAARO3AAFAAACY4AAB';
0002001100000171 T_FLASH_TRANS INSERT delete from YANGTK.T_FLASH_TRANS where ROWID = 'AAARO3AAFAAACY4AAA';
000600180000016D T_FLASH_TRANS INSERT delete from YANGTK.T_FLASH_TRANS where ROWID = 'AAARO3AAFAAACY4AAC';
000600180000016D T_FLASH_TRANS INSERT delete from YANGTK.T_FLASH_TRANS where ROWID = 'AAARO3AAFAAACY4AAD';
000600180000016D T_FLASH_TRANS INSERT delete from YANGTK.T_FLASH_TRANS where ROWID = 'AAARO3AAFAAACY4AAE';
000600180000016D T_FLASH_TRANS INSERT delete from YANGTK.T_FLASH_TRANS where ROWID = 'AAARO3AAFAAACY4AAF';
6 rows selected.
.
[/PHP]
对于T_FLASH_TRANS表存在两个事务，分别插入了2条和4条记录，下面利用SYS来撤销掉第一个事务：
[PHP]
SQL> CONN / AS SYSDBA
Connected.
SQL> DECLARE
2 V_XID XID_ARRAY;
3 BEGIN
4 V_XID := SYS.XID_ARRAY('0002001100000171');
5 DBMS_FLASHBACK.TRANSACTION_BACKOUT(1, V_XID);
6 END;
7 /
PL/SQL procedure successfully completed.
.
[/PHP]
下面看看T_FLASH_TRANS表中的数据：
[PHP]
SQL> SELECT * FROM YANGTK.T_FLASH_TRANS;
ID NAME
---------- ------------------------------
4 C
5 C
6 C
7 C
.
[/PHP]
原文出自：[url]http://space.itpub.net/4227/viewspace-69520[/url]
[[i] 本帖最后由 yangtingkun 于 2008-1-11 09:35 编辑 [/i]]
2008-1-11 08:10
yangtingkun
上面一篇简单介绍了如何撤销一个事务，那个例子中，被撤销事务和其他事务没有关联。如果撤销一个事务的时候发现被撤销事务和其他事务级联，那么会有多种情况。
[PHP]
SQL> CONN YANGTK/yangtk@172.0.2.61/test11g.netdb
已连接。
SQL> CREATE TABLE T_FLASH_TRANS (ID NUMBER, NAME VARCHAR2(30));
表已创建。
SQL> INSERT INTO T_FLASH_TRANS VALUES (1, 'A');
已创建 1 行。
SQL> INSERT INTO T_FLASH_TRANS VALUES (2, 'B');
已创建 1 行。
SQL> COMMIT;
提交完成。
SQL> UPDATE T_FLASH_TRANS SET NAME = NAME || '1';
已更新2行。
SQL> INSERT INTO T_FLASH_TRANS VALUES (3, 'C');
已创建 1 行。
SQL> COMMIT;
提交完成。
SQL> UPDATE T_FLASH_TRANS SET NAME = NAME || '2' WHERE ID = 1;
已更新 1 行。
SQL> INSERT INTO T_FLASH_TRANS VALUES (4, 'D');
已创建 1 行。
SQL> COMMIT;
提交完成。
SQL> CONN SYS/[email]test@172.0.2.61[/email]/TEST11G.NETDB AS SYSDBA
已连接。
SQL> SELECT XID, OPERATION, UNDO_SQL
2 FROM FLASHBACK_TRANSACTION_QUERY
3 WHERE TABLE_NAME = 'T_FLASH_TRANS';
XID OPERATION UNDO_SQL
---------------- ---------- ------------------------------------------------------------------------
0004001A00000344 INSERT delete from YANGTK.T_FLASH_TRANS where ROWID = 'AAARskAAFAAACY3AAC';
0004001A00000344 UPDATE update YANGTK.T_FLASH_TRANS set NAME = 'B' where ROWID = 'AAARskAA
0004001A00000344 UPDATE update YANGTK.T_FLASH_TRANS set NAME = 'A' where ROWID = 'AAARskAA
0006002F00000345 INSERT delete from YANGTK.T_FLASH_TRANS where ROWID = 'AAARskAAFAAACY3AAD';
0006002F00000345 UPDATE update YANGTK.T_FLASH_TRANS set NAME = 'A1' where ROWID = 'AAARskA
000A005000000343 INSERT delete from YANGTK.T_FLASH_TRANS where ROWID = 'AAARskAAFAAACY3AAB';
000A005000000343 INSERT delete from YANGTK.T_FLASH_TRANS where ROWID = 'AAARskAAFAAACY3AAA';
已选择7行。
.
[/PHP]
下面对第一个更新进行撤销：
[PHP]
SQL> DECLARE
2 V_XID XID_ARRAY;
3 BEGIN
4 V_XID := SYS.XID_ARRAY('0004001A00000344');
5 DBMS_FLASHBACK.TRANSACTION_BACKOUT(1, V_XID);
6 END;
7 /
DECLARE
*
第 1 行出现错误:
ORA-55504: NOCASCADE 模式下的事务处理冲突
ORA-06512: 在 SYS.DBMS_FLASHBACK, line 37
ORA-06512: 在 SYS.DBMS_FLASHBACK, line 70
ORA-06512: 在 line 5
.
[/PHP]
撤销出错，这是由于要撤销第二个事务，这个事务对ID为1的记录进行了更新，但是在随后的事务中又对这条记录进行了修改。也就是说这个事务是后面事务的基础，这时没有办法仅仅撤销前面的事务而不影响后面的事务。
对于这种情况，Oracle有三种不同的解决方法，其中最好理解的方法是CASCADE方式：
[PHP]
SQL> DECLARE
2 V_XID XID_ARRAY;
3 BEGIN
4 V_XID := SYS.XID_ARRAY('0004001A00000344');
5 DBMS_FLASHBACK.TRANSACTION_BACKOUT(1, V_XID, DBMS_FLASHBACK.CASCADE);
6 END;
7 /
PL/SQL 过程已成功完成。
SQL> SELECT * FROM YANGTK.T_FLASH_TRANS;
ID NAME
---------- ------------------------------
1 A
2 B
.
[/PHP]
使用CASCADE方式，会根据事务提交的相反顺序，依次撤销所有关联的事务。
在这个例子中，Oracle不但撤销了第一个UPDATE事务，而且撤销了随后的UPDATE和INSERT事务。
下面看第二种方式，NONCONFLICT_ONLY。在采用这种方式之前，先要说明一下，TRANSACTION_BACKOUT过程是事务性的，也就是说，如果发现撤销事务后，得到的不是预期的结果，还可以通过ROLLBACK来回滚事务撤销操作本身：
[PHP]
SQL> ROLLBACK;
回退已完成。
SQL> SELECT * FROM YANGTK.T_FLASH_TRANS;
ID NAME
---------- ------------------------------
1 A12
2 B1
3 C
4 D
.
[/PHP]
下面采用NONCONFLICT_ONLY方式进行事务撤销：
[PHP]
SQL> DECLARE
2 V_XID XID_ARRAY;
3 BEGIN
4 V_XID := SYS.XID_ARRAY('0004001A00000344');
5 DBMS_FLASHBACK.TRANSACTION_BACKOUT(1, V_XID, DBMS_FLASHBACK.NONCONFLICT_ONLY);
6 END;
7 /
PL/SQL 过程已成功完成。
SQL> SELECT * FROM YANGTK.T_FLASH_TRANS;
ID NAME
---------- ------------------------------
1 A12
2 B
4 D
.
[/PHP]
对于NONCONFLICT_ONLY方式，Oracle会回滚指定事务中没有被关联的部分。比如这个例子中，UPDATE语句被后面的事务关联，但是INSERT语句并没有被关联。因此，撤销了事务中的INSERT语句，对于UPDATE语句，只有关联的那条数据没有被撤销，而没有被关联的数据则被撤销成功。这种方式可以保证数据库的一致性，但是会损失事务的完整性。
Oracle文档上还描述了一种方式NOCASCADE_FORCE：
[PHP]
SQL> ROLLBACK;
回退已完成。
SQL> SELECT * FROM YANGTK.T_FLASH_TRANS;
ID NAME
---------- ------------------------------
1 A12
2 B1
3 C
4 D
SQL> DECLARE
2 V_XID XID_ARRAY;
3 BEGIN
4 V_XID := SYS.XID_ARRAY('0004001A00000344');
5 DBMS_FLASHBACK.TRANSACTION_BACKOUT(1, V_XID, DBMS_FLASHBACK.NOCASCADE_FORCE);
6 END;
7 /
PL/SQL 过程已成功完成。
SQL> SELECT * FROM YANGTK.T_FLASH_TRANS;
ID NAME
---------- ------------------------------
1 A12
2 B
4 D
.
[/PHP]
根据文档上的描述，个人感觉采用这种方式的撤销后，ID为1的记录NAME应该为A而不是A12，现在的这种结果与NONCONFLICT_ONLY方式没有差别，怀疑目前的结果是个bug。不过实际如何只能等Oracle推出补丁或者声明bug才能了解。
原文出自：[url]http://space.itpub.net/4227/viewspace-69549[/url]
[[i] 本帖最后由 yangtingkun 于 2008-1-11 09:42 编辑 [/i]]
[PHP]
SQL> CONN YANGTK/yangtk@172.0.2.61/test11g.netdb
已连接。
SQL> CREATE TABLE T_FLASH_TRANS (ID NUMBER, NAME VARCHAR2(30));
表已创建。
SQL> INSERT INTO T_FLASH_TRANS VALUES (1, 'A');
已创建 1 行。
SQL> INSERT INTO T_FLASH_TRANS VALUES (2, 'B');
已创建 1 行。
SQL> COMMIT;
提交完成。
SQL> UPDATE T_FLASH_TRANS SET NAME = NAME || '1';
已更新2行。
SQL> INSERT INTO T_FLASH_TRANS VALUES (3, 'C');
已创建 1 行。
SQL> COMMIT;
提交完成。
SQL> UPDATE T_FLASH_TRANS SET NAME = NAME || '2' WHERE ID = 1;
已更新 1 行。
SQL> INSERT INTO T_FLASH_TRANS VALUES (4, 'D');
已创建 1 行。
SQL> COMMIT;
提交完成。
SQL> CONN SYS/[email]test@172.0.2.61[/email]/TEST11G.NETDB AS SYSDBA
已连接。
SQL> SELECT XID, OPERATION, UNDO_SQL
2 FROM FLASHBACK_TRANSACTION_QUERY
3 WHERE TABLE_NAME = 'T_FLASH_TRANS';
XID OPERATION UNDO_SQL
---------------- ---------- ------------------------------------------------------------------------
0004001A00000344 INSERT delete from YANGTK.T_FLASH_TRANS where ROWID = 'AAARskAAFAAACY3AAC';
0004001A00000344 UPDATE update YANGTK.T_FLASH_TRANS set NAME = 'B' where ROWID = 'AAARskAA
0004001A00000344 UPDATE update YANGTK.T_FLASH_TRANS set NAME = 'A' where ROWID = 'AAARskAA
0006002F00000345 INSERT delete from YANGTK.T_FLASH_TRANS where ROWID = 'AAARskAAFAAACY3AAD';
0006002F00000345 UPDATE update YANGTK.T_FLASH_TRANS set NAME = 'A1' where ROWID = 'AAARskA
000A005000000343 INSERT delete from YANGTK.T_FLASH_TRANS where ROWID = 'AAARskAAFAAACY3AAB';
000A005000000343 INSERT delete from YANGTK.T_FLASH_TRANS where ROWID = 'AAARskAAFAAACY3AAA';
已选择7行。
.
[/PHP]
下面对第一个更新进行撤销：
[PHP]
SQL> DECLARE
2 V_XID XID_ARRAY;
3 BEGIN
4 V_XID := SYS.XID_ARRAY('0004001A00000344');
5 DBMS_FLASHBACK.TRANSACTION_BACKOUT(1, V_XID);
6 END;
7 /
DECLARE
*
第 1 行出现错误:
ORA-55504: NOCASCADE 模式下的事务处理冲突
ORA-06512: 在 SYS.DBMS_FLASHBACK, line 37
ORA-06512: 在 SYS.DBMS_FLASHBACK, line 70
ORA-06512: 在 line 5
.
[/PHP]
撤销出错，这是由于要撤销第二个事务，这个事务对ID为1的记录进行了更新，但是在随后的事务中又对这条记录进行了修改。也就是说这个事务是后面事务的基础，这时没有办法仅仅撤销前面的事务而不影响后面的事务。
对于这种情况，Oracle有三种不同的解决方法，其中最好理解的方法是CASCADE方式：
[PHP]
SQL> DECLARE
2 V_XID XID_ARRAY;
3 BEGIN
4 V_XID := SYS.XID_ARRAY('0004001A00000344');
5 DBMS_FLASHBACK.TRANSACTION_BACKOUT(1, V_XID, DBMS_FLASHBACK.CASCADE);
6 END;
7 /
PL/SQL 过程已成功完成。
SQL> SELECT * FROM YANGTK.T_FLASH_TRANS;
ID NAME
---------- ------------------------------
1 A
2 B
.
[/PHP]
使用CASCADE方式，会根据事务提交的相反顺序，依次撤销所有关联的事务。
在这个例子中，Oracle不但撤销了第一个UPDATE事务，而且撤销了随后的UPDATE和INSERT事务。
下面看第二种方式，NONCONFLICT_ONLY。在采用这种方式之前，先要说明一下，TRANSACTION_BACKOUT过程是事务性的，也就是说，如果发现撤销事务后，得到的不是预期的结果，还可以通过ROLLBACK来回滚事务撤销操作本身：
[PHP]
SQL> ROLLBACK;
回退已完成。
SQL> SELECT * FROM YANGTK.T_FLASH_TRANS;
ID NAME
---------- ------------------------------
1 A12
2 B1
3 C
4 D
.
[/PHP]
下面采用NONCONFLICT_ONLY方式进行事务撤销：
[PHP]
SQL> DECLARE
2 V_XID XID_ARRAY;
3 BEGIN
4 V_XID := SYS.XID_ARRAY('0004001A00000344');
5 DBMS_FLASHBACK.TRANSACTION_BACKOUT(1, V_XID, DBMS_FLASHBACK.NONCONFLICT_ONLY);
6 END;
7 /
PL/SQL 过程已成功完成。
SQL> SELECT * FROM YANGTK.T_FLASH_TRANS;
ID NAME
---------- ------------------------------
1 A12
2 B
4 D
.
[/PHP]
对于NONCONFLICT_ONLY方式，Oracle会回滚指定事务中没有被关联的部分。比如这个例子中，UPDATE语句被后面的事务关联，但是INSERT语句并没有被关联。因此，撤销了事务中的INSERT语句，对于UPDATE语句，只有关联的那条数据没有被撤销，而没有被关联的数据则被撤销成功。这种方式可以保证数据库的一致性，但是会损失事务的完整性。
Oracle文档上还描述了一种方式NOCASCADE_FORCE：
[PHP]
SQL> ROLLBACK;
回退已完成。
SQL> SELECT * FROM YANGTK.T_FLASH_TRANS;
ID NAME
---------- ------------------------------
1 A12
2 B1
3 C
4 D
SQL> DECLARE
2 V_XID XID_ARRAY;
3 BEGIN
4 V_XID := SYS.XID_ARRAY('0004001A00000344');
5 DBMS_FLASHBACK.TRANSACTION_BACKOUT(1, V_XID, DBMS_FLASHBACK.NOCASCADE_FORCE);
6 END;
7 /
PL/SQL 过程已成功完成。
SQL> SELECT * FROM YANGTK.T_FLASH_TRANS;
ID NAME
---------- ------------------------------
1 A12
2 B
4 D
.
[/PHP]
根据文档上的描述，个人感觉采用这种方式的撤销后，ID为1的记录NAME应该为A而不是A12，现在的这种结果与NONCONFLICT_ONLY方式没有差别，怀疑目前的结果是个bug。不过实际如何只能等Oracle推出补丁或者声明bug才能了解。
原文出自：[url]http://space.itpub.net/4227/viewspace-69549[/url]
[[i] 本帖最后由 yangtingkun 于 2008-1-11 09:42 编辑 [/i]]
2008-1-11 08:15
yangtingkun
上面一篇文章简单介绍了撤销事务的几种选项，这几种选项是对关联事务而言的，对于事务之前的主外键约束，结果又是不同的。
先建立测试环境：
[PHP]
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/[email]test@172.0.2.61[/email]/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';
.
[/PHP]
下面开始进行撤销测试：
[PHP]
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
.
[/PHP]
由于要撤销的事务中包括对主表记录的插入，而这条记录被子表引用，因此撤销肯定会出现错误。尝试使用CASCADE方式进行撤销：
[PHP]
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
.
[/PHP]
CASCADE方式只对关联事务有效，而对这种主外键关系无效，因此，如果撤销事务操作会违反主外键约束，那么即使使用CASCADE方式也没有作用。
同样的，另外两种方式NONCONFLICT_ONLY和NOCASCADE_FORCE也没有作用：
[PHP]
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
.
[/PHP]
唯一的方法是包括参考主键信息的外键事务一起进行撤销：
[PHP]
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
.
[/PHP]
感觉这一点上撤销事务的实现有点问题，主外键约束事务的撤销应该可以仿效关联事务的方式实现。否则撤销事务在实际工作中就会很难使用。
原文出自：[url]http://space.itpub.net/4227/viewspace-69550[/url]
[[i] 本帖最后由 yangtingkun 于 2008-1-11 09:47 编辑 [/i]]
先建立测试环境：
[PHP]
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/[email]test@172.0.2.61[/email]/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';
.
[/PHP]
下面开始进行撤销测试：
[PHP]
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
.
[/PHP]
由于要撤销的事务中包括对主表记录的插入，而这条记录被子表引用，因此撤销肯定会出现错误。尝试使用CASCADE方式进行撤销：
[PHP]
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
.
[/PHP]
CASCADE方式只对关联事务有效，而对这种主外键关系无效，因此，如果撤销事务操作会违反主外键约束，那么即使使用CASCADE方式也没有作用。
同样的，另外两种方式NONCONFLICT_ONLY和NOCASCADE_FORCE也没有作用：
[PHP]
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
.
[/PHP]
唯一的方法是包括参考主键信息的外键事务一起进行撤销：
[PHP]
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
.
[/PHP]
感觉这一点上撤销事务的实现有点问题，主外键约束事务的撤销应该可以仿效关联事务的方式实现。否则撤销事务在实际工作中就会很难使用。
原文出自：[url]http://space.itpub.net/4227/viewspace-69550[/url]
[[i] 本帖最后由 yangtingkun 于 2008-1-11 09:47 编辑 [/i]]
2008-1-11 09:28
warehouse
好文
oracle越弄越复杂啊
禁用 URL 识别
禁用 Smilies
看看效果好不
oracle越弄越复杂啊
禁用 URL 识别
禁用 Smilies
看看效果好不
2008-1-11 09:40
yangtingkun
[quote]原帖由 [i]warehouse[/i] 于 2008-1-11 09:28 发表 [url=http://www.itpub.net/redirect.php?goto=findpost&pid=9385138&ptid=925356][img]http://www.itpub.net/images/common/back.gif[/img][/url]
好文
oracle越弄越复杂啊
禁用 URL 识别
禁用 Smilies
看看效果好不 [/quote]
我都没注意，出了这么多奇怪的东西，以前PHP代码中的东西不再解析的啊
:sweat2:
好文
oracle越弄越复杂啊
禁用 URL 识别
禁用 Smilies
看看效果好不 [/quote]
我都没注意，出了这么多奇怪的东西，以前PHP代码中的东西不再解析的啊
:sweat2:
2008-1-11 09:45
yangtingkun
除非禁止Discuz!代码，否则总有这些"东西。
而禁止了Discuz!代码，PHP的效果就没有了。:sweat2:
我查了一下，是由于双引号引起的问题，好在这篇文章中所有的双引号都没有什么实际的意义，大部分是Oracle自己生成的，我把所有的双引号的去掉了
而禁止了Discuz!代码，PHP的效果就没有了。:sweat2:
我查了一下，是由于双引号引起的问题，好在这篇文章中所有的双引号都没有什么实际的意义，大部分是Oracle自己生成的，我把所有的双引号的去掉了
2008-1-11 14:11
bluemoon0083
学习
2008-1-11 16:24
rainytag
这个也是太复杂了，实际应用在什么地方呢
2008-1-11 16:56
hc_yao
学习
楼主真强，以后要跟楼主好好学习！
楼主真强，以后要跟楼主好好学习！
2008-1-14 10:27
yudingchu
学习了
2008-1-16 10:28
ayh508
truncate表能回滚么
truncate表能回滚么？
truncate表能回滚么？
2008-1-16 18:15
小人物
很详细,谢谢楼主
2008-1-17 10:06
yangtingkun
[quote]原帖由 [i]ayh508[/i] 于 2008-1-16 10:28 发表 [url=http://www.itpub.net/redirect.php?goto=findpost&pid=9424144&ptid=925356][img]http://www.itpub.net/images/common/back.gif[/img][/url]
truncate表能回滚么？ [/quote]
不行，前提是表不能执行DDL语句
truncate表能回滚么？ [/quote]
不行，前提是表不能执行DDL语句
2008-1-19 09:18
zgongjin
学习
2008-1-20 08:51
lsw_ruc
果然是精华
2008-1-20 08:51
lsw_ruc
学习一下
2008-4-8 17:44
cainiao2
学习
2008-5-3 14:12
chengboxian
目前还不知道在应用中会不会有这样的逻辑，感觉就是有点搞……
2008-5-6 17:37
foxmile
老杨有用过这个特性恢复数据么。
2008-5-6 23:47
yuxuan
感觉oracle11g许多东西不是很实用
oracle应该在稳定性上下功夫
oracle应该在稳定性上下功夫
页: [1] 2
查看完整版本: Oracle11新特性——撤销事务
Powered by ITPUB论坛