Rollback Commited Transactions
Rollback Commited Transactions
Reference (Chinese)
http://www.itpub.net/viewthread.php?tid=925356&extra=&page=1
though oracle provides flash back query from oracle 9i, the recovery needs several operations.
oracle 11g provides DBMS_FLASHBACK.TRANSACTION_BACKOUT package to simplize the process.
requirement
- ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
- undo_retention and undo tablespace size
- sysdba privilege for logminer
and there are 3 mode for backout
- DBMS_FLASHBACK.CASCADE
- DBMS_FLASHBACK.NONCONFLICT_ONLY
- DBMS_FLASHBACK.NOCASCADE_FORCE
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. -- there are 2 transactions. let's rollback one 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. SQL> SELECT * FROM YANGTK.T_FLASH_TRANS; ID NAME ---------- ------------------------------ 4 C 5 C 6 C 7 C -- three rollback mode 1 DBMS_FLASHBACK.CASCADE 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 / SQL> SELECT * FROM YANGTK.T_FLASH_TRANS; ID NAME ---------- ------------------------------ 1 A 2 B -- mode 2 DBMS_FLASHBACK.NONCONFLICT_ONLY 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 -- mode 3 DBMS_FLASHBACK.NOCASCADE_FORCE 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
TRANSACTION_BACKOUT is transactional, that's you can rollback the rollback with rollback command.