oracle11g回闪,oracle11g使用DBMS_FLASHBACK闪回撤消事务

oracle11g使用DBMS_FLASHBACK闪回撤消事务

Steps to Undo Transactions by Using DBMS_FLASHBACK

Package

OS: Oracle Linux Server release 6.4

Linux dbserver 2.6.39-400.17.1.el6uek.i686 #1 SMP Fri Feb 22

18:17:46 PST 2013 i686 i686 i386 GNU/Linux

Database：oracle 11g 11.2.0.4

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

$ sqlplus /nolog

SQL> CONN / AS SYSDBA

SQL> CREATE TABLE t_flash_trans (id NUMBER, name

VARCHAR2(20));

SQL> INSERT INTO t_flash_trans VALUES (1, 'jason');

SQL> INSERT INTO t_flash_trans VALUES (2, 'iris');

SQL> COMMIT;

SQL> SELECT rowid, id, name FROM t_flash_trans;

ROWID  ID NAME

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

AAAVlyAAEAAAAIPAAA  1

jason

AAAVlyAAEAAAAIPAAB  2

iris

SQL> INSERT INTO t_flash_trans SELECT 3 + ROWNUM, 'jason'

FROM t_flash_trans;

SQL> COMMIT;

SQL> SELECT rowid, id, name FROM t_flash_trans;

ROWID  ID NAME

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

AAAVlyAAEAAAAIPAAA  1

jason

AAAVlyAAEAAAAIPAAB  2

iris

AAAVlyAAEAAAAIPAAC  4

jason

AAAVlyAAEAAAAIPAAD  5

jason

SQL> SELECT xid, table_name, operation, undo_sql FROM

flashback_transaction_query WHERE table_name =

'T_FLASH_TRANS';

XID  TABLE_NAME  OPERATION  UNDO_SQL

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

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

03001D000E030000 T_FLASH_TRANS  INSERT

delete from

"HR"."T_FLASH_TRANS" where ROWID = 'AAAVlyAAEAAAAIPAAC';

03001D000E030000 T_FLASH_TRANS  INSERT

delete from

"HR"."T_FLASH_TRANS" where ROWID = 'AAAVlyAAEAAAAIPAAD';

0500130001030000 T_FLASH_TRANS  INSERT

delete from

"HR"."T_FLASH_TRANS" where ROWID = 'AAAVlyAAEAAAAIPAAB';

0500130001030000 T_FLASH_TRANS  INSERT

delete from

"HR"."T_FLASH_TRANS" where ROWID = 'AAAVlyAAEAAAAIPAAA';

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

SQL> CONN / AS SYSDBA

SQL> DECLARE

v_xid SYS.XID_ARRAY;

BEGIN

v_xid :=

SYS.XID_ARRAY('03001D000E030000');

DBMS_FLASHBACK.TRANSACTION_BACKOUT(1,v_xid);

END;

/

SQL> COMMIT;

SQL> SELECT * FROM t_flash_trans;

ID NAME

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

1 jason

2 iris

注意:撤消事务有以下几个前提条件:

1.数据库必须归档

SQL> ARCHIVE LOG LIST;

2.允许补充日志数据可用。

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

3.补充日志数据 (主键) 可用。

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA ( PRIMARY KEY

) COLUMNS;

!!The End!!