oracle取消正在回滚的事务,Oracle11新特性——撤销事务（一）

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

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

这一篇简单介绍撤销事务的用法。

Oracle的发起一个事务后，可以选择提交或者回滚。回滚的话，会恢复到这个事务发生之前的状态，而一旦提交，这个事务的所有修改都被保存到数据库中。

从11g开始，Oracle提供了撤销一个已经提交事务的能力。这个功能是通过DBMS_FLASHBACK.TRANSACTION_BACKOUT过程实现的。

首先建立一张测试表：

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';

找到要撤销的事务后，通过调用DBMS_FLASHBACK.TRANSACTION_BACKOUT过程：

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

系统报错没有权限，当前用户拥有DBA角色，仍然权限不足，看来必须要用SYS来执行：

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.

使用SYS执行报错如下：

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

对于这个错误，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.

检查上面这几项，似乎只是违反了第三项：

SQL> SELECT LOG_MODE, SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

LOG_MODE SUPPLEME

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

ARCHIVELOG NO

下面设置数据库SUPPLEMENTAL LOG DATA：

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

这个错误造成的原因显然是由于INSERT语句发生在设置SUPPLEMENTAL LOG DATA之后，Oracle在LOG中无法找到这个事务。

而且，如果执行与事务发生的间隔超过了UNDO_RETENTION的设置，那么还可能出现下面的错误：

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

从上面的错误已经可以看到，Oracle的撤销事务是通过FLASHBACK查询配合LOGMINER进行的。由于LOGMINER操作只能SYS来进行操作，因此撤销事务也必须使用SYS用户。

了解了使用撤销事务可能碰到的问题，下面看一个真正的例子：

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.

对于T_FLASH_TRANS表存在两个事务，分别插入了2条和4条记录，下面利用SYS来撤销掉第一个事务：

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.

下面看看T_FLASH_TRANS表中的数据：

SQL> SELECT * FROM YANGTK.T_FLASH_TRANS;

ID NAME

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

4 C

5 C

6 C

7 C