[bbk4785] 第33集 - 第三章 Flashback Table 10

/************************************************************************************/

实验目的:使用DBMS_FLASHBACK.TRANSACTION_BACKOUT回退事务

实验步骤:

　　1、emp-insert into-empno=1

　　2、emp-insert into-empno=2

　　3、flashback transaction query--定位

　　4、flashback transaction--recover

/************************************************************************************/

SQL> conn SCOTT/TIGER
Connected.
SQL> create table emp3 as select * from emp;

Table created.

SQL> select empno,ename,sal from emp3;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH             800
      7499 ALLEN            1600
      7521 WARD             1250
      7566 JONES            2975
      7654 MARTIN           1250
      7698 BLAKE            2850
      7782 CLARK            2450
      7788 SCOTT            3000
      7839 KING             5000
      7844 TURNER           1500
      7876 ADAMS            1100

     EMPNO ENAME             SAL
---------- ---------- ----------
      7900 JAMES             950
      7902 FORD             3000
      7934 MILLER           1300

14 rows selected.

SQL> insert into emp3(empno,ename,sal) values(1,'zhangsan1',10);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into emp3(empno,ename,sal) values(2,'zhangsan2',20);

1 row created.

SQL> commit;

Commit complete.

SQL> select xid,to_char(commit_timestamp,'yyyy-mm-dd hh24:mi:ss') commit_timestamp,operation,undo_sql from flashback_transaction_query where table_owner = 'SCOTT' and table_name = 'EMP3';

XID              COMMIT_TIMESTAMP    OPERATION    UNDO_SQL
---------------- ------------------- ------------ ----------------------------------------
02000B00F9080000 2013-05-17 19:44:16 INSERT       delete from "SCOTT"."EMP3" where ROWID =
                                                   'AAATn7AAEAAABCFAAA';

07001D0020080000 2013-05-17 19:44:34 INSERT       delete from "SCOTT"."EMP3" where ROWID =
                                                   'AAATn7AAEAAABCFAAB';

SQL> conn /as sysdba
Connected.
SQL> 
  1  DECLARE
  2  V_XID sys.XID_ARRAY;
  3  BEGIN
  4  V_XID :=SYS.XID_ARRAY('07001D0020080000');
  5  DBMS_FLASHBACK.TRANSACTION_BACKOUT(1,V_XID);
  6* END;
SQL> /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.