Edu 11g New Features Jp

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.