打算写一系列的文章介绍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