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
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/28389881/viewspace-1667845/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle11新特性——撤銷事務(三)Oracle
- Oracle11新特性——撤銷事務(二)Oracle
- Oracle11新特性——PLSQL新特性(一)OracleSQL
- Oracle11新特性——PLSQL新特性(七)OracleSQL
- Oracle11新特性——PLSQL新特性(六)OracleSQL
- Oracle11新特性——PLSQL新特性(五)OracleSQL
- Oracle11新特性——PLSQL新特性(四)OracleSQL
- Oracle11新特性——PLSQL新特性(三)OracleSQL
- Oracle11新特性——PLSQL新特性(二)OracleSQL
- Oracle11新特性——行列轉換語句(一)Oracle
- Oracle11新特性——分割槽功能增強(一)Oracle
- Oracle11新特性——虛擬列Oracle
- Oracle11新特性——虛擬列(二)Oracle
- Oracle11新特性——備份恢復功能增強(一)Oracle
- Oracle11新特性——PLSQL函式快取結果(一)OracleSQL函式快取
- Oracle11新特性——線上操作功能增強(一)Oracle
- git進階(撤銷pull、撤銷merge、撤銷add)Git
- Oracle11新特性——分割槽功能增強Oracle
- 網頁撤銷後ubuntu本地撤銷網頁Ubuntu
- Oracle11新特性——分割槽功能增強(五)Oracle
- Oracle11新特性——分割槽功能增強(四)Oracle
- Oracle11新特性——行列轉換語句(二)Oracle
- Oracle11新特性——分割槽功能增強(三)Oracle
- Oracle11新特性——分割槽功能增強(二)Oracle
- 認識 MongoDB 4.0 的新特性——事務(Transactions)MongoDB
- 什麼是事務、事務特性、事務隔離級別、spring事務傳播特性?Spring
- Oracle11新特性——備份恢復功能增強Oracle
- Oracle11新特性——SQL快取結果集(五)OracleSQL快取
- Oracle11新特性——SQL快取結果集(三)OracleSQL快取
- ❤️🔥 Solon Cloud Event 新的事務特性與應用Cloud
- git撤銷命令Git
- Oracle11新特性——備份恢復功能增強(六)Oracle
- Oracle11新特性——備份恢復功能增強(五)Oracle
- Oracle11新特性——備份恢復功能增強(四)Oracle
- Oracle11新特性——備份恢復功能增強(三)Oracle
- Oracle11新特性——備份恢復功能增強(二)Oracle
- Oracle11新特性——備份恢復功能增強(十)Oracle
- Oracle11新特性——備份恢復功能增強(九)Oracle