开启辅助访问

Bo's Oracle Station

Bo's Oracle Station»社区 › 培训进行时（结课后图标变灰，但是欢迎继续发帖！） › 4077-12c管理类OCP认证班第四期 › 课程第22次（2018-03-26星期一）
返回列表 发新帖
查看: 1781|回复: 0

课程第22次（2018-03-26星期一）

[复制链接]
botang

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2018-3-26 19:43:16 | 显示全部楼层 |阅读模式
t05310.sql:
  1. undefine s1
  2. create table &&s1( id number ) ;
  3. ! sleep 5
  4. insert into &&s1 values (1) ;
  5. insert into &&s1 values (2) ;
  6. insert into &&s1 values (3) ;
  7. commit;
  8. update &&s1 set id=11 where id=1;
  9. update &&s1 set id=22 where id=2;
  10. update &&s1 set id=33 where id=3;
  11. commit;
  12. update &&s1 set id=222 where id=22;
  13. update &&s1 set id=333 where id=33;
  14. commit;
  15. update &&s1 set id=22 where id=222;
  16. commit;
复制代码

闪回事务又名撤销事务（Transaction Backout），能够撤销一个或多个事务的修改，其功能由一个名为DBMS_FLASHBACK.TRANSACTION_BACKOUT的存储过程实现。该存储过程的工作原理是自动分析重做日志，挖掘出变更前的值用以构建撤销SQL（Undo SQL），然后执行撤销SQL最后达到撤销事务的目的。

为了该功能可以正常使用，至少需要事先启用主键补充日志。另外，为了能够跟踪外键依赖还需要启用外键补充日志。

在继续讨论此功能前，首先应了解一个概念：事务的依赖性。比如，两个事务TX1和TX2，若符合以下3个条件的任意一个就可以认为TX2依赖TX1：

（1）WAW依赖（Write After Write），即在TX1修改了表的某行之后，TX2又修改了同一行。

（2）主键依赖，即在一张拥有主键的表中TX1首先删除了一行，之后TX2又插入了具有相同主键值的另一行。

（3）外建依赖，即由于TX1的修改（insert或update）而产生了新的可被外键参考的字段值，之后TX2修改（insert或update）外键字段时利用了TX1所产生的字段值。

  1. SQL> desc dbms_flashback
  2. PROCEDURE DISABLE
  3. PROCEDURE ENABLE_AT_SYSTEM_CHANGE_NUMBER
  4. Argument Name                        Type                        In/Out Default?
  5. ------------------------------ ----------------------- ------ --------
  6. QUERY_SCN                        NUMBER                        IN
  7. PROCEDURE ENABLE_AT_TIME
  8. Argument Name                        Type                        In/Out Default?
  9. ------------------------------ ----------------------- ------ --------
  10. QUERY_TIME                        TIMESTAMP                IN
  11. FUNCTION GET_SYSTEM_CHANGE_NUMBER RETURNS NUMBER
  12. PROCEDURE TRANSACTION_BACKOUT
  13. Argument Name                        Type                        In/Out Default?
  14. ------------------------------ ----------------------- ------ --------
  15. NUMTXNS                        NUMBER                        IN
  16. XIDS                                XID_ARRAY                IN
  17. OPTIONS                        BINARY_INTEGER                IN     DEFAULT
  18. SCNHINT                        NUMBER                        IN     DEFAULT
  19. PROCEDURE TRANSACTION_BACKOUT
  20. Argument Name                        Type                        In/Out Default?
  21. ------------------------------ ----------------------- ------ --------
  22. NUMTXNS                        NUMBER                        IN
  23. XIDS                                XID_ARRAY                IN
  24. OPTIONS                        BINARY_INTEGER                IN     DEFAULT
  25. TIMEHINT                        TIMESTAMP                IN
  26. PROCEDURE TRANSACTION_BACKOUT
  27. Argument Name                        Type                        In/Out Default?
  28. ------------------------------ ----------------------- ------ --------
  29. NUMTXNS                        NUMBER                        IN
  30. NAMES                                TXNAME_ARRAY                IN
  31. OPTIONS                        BINARY_INTEGER                IN     DEFAULT
  32. SCNHINT                        NUMBER                        IN     DEFAULT
  33. PROCEDURE TRANSACTION_BACKOUT
  34. Argument Name                        Type                        In/Out Default?
  35. ------------------------------ ----------------------- ------ --------
  36. NUMTXNS                        NUMBER                        IN
  37. NAMES                                TXNAME_ARRAY                IN
  38. OPTIONS                        BINARY_INTEGER                IN     DEFAULT
  39. TIMEHINT                        TIMESTAMP                IN

  41. SQL>
复制代码

TRANSACTION_BACKOUT存储过程的OPTIONS参数就是为了解决事务依赖性问题而存在的，在该参数上管理员可以使用4种撤销事务的方案，假设被撤销的事务是TX1，若其具有依赖事务，则称为TX2：

（1）NOCASCADE，TX1不可以被任何其他事务依赖（即TX2不存在），否则撤销操作报错。

（2）CASCADE，将TX1连同TX2一起撤销。

（3）NOCASCADE_FORCE，忽略TX2，直接执行TX1的撤销SQL将TX1撤销，如果没有约束上的冲突，操作将成功，否则约束报错导致撤销操作失败。

（4破坏事务TX1的原子性）NONCONFILICT_ONLY，在不影响TX2的前提下，撤销TX1的修改。与NOCASCADE_FORCE的不同点在于会首先过滤一下TX1的撤销SQL(补偿事务)，确保它们不会作用在TX2修改的行上。

  1. 若采用NOCASCADE结果是抛出错误“ORA-55504: Transaction conflicts in NOCASCADE mode”，表内容依然是：

  3. ID
  4. ------
  5.   11
  6.   22
  7. 333
复制代码
NOCASCADE：
  1. select  *  from flashback_transaction_query
  2. where table_name='T_NOCASCADE'  and logon_user='HR'  order by 2 ;

  4. begin
  5.   dbms_flashback.transaction_backout(numtxns => 1,
  6.                                                                 xids =>  xid_array('0E0007002C000000'),
  7.                                                                 options => dbms_flashback.nocascade);
  8. end;
复制代码
CASCADE：
报告的过程：
  1. <?xml version="1.0" encoding="ISO-8859-1"?>
  2. <COMP_XID_REPORT XID="0F0017002C000000">
  3.         <TRANSACTION XID="13000D002D000000">
  4.         <CHARACTERISTICS>
  5.         </CHARACTERISTICS>
  6.         <UNDO_SQL>
  7.                 <USQL exec="yes">
  8.                  update "HR"."T_CASCADE" set "ID" = '3' where "ID" = '33' and ROWID = 'AAASQAAAEAAAAIkAAC'
  9.                 </USQL>
  10.                 <USQL exec="yes">
  11.                  update "HR"."T_CASCADE" set "ID" = '2' where "ID" = '22' and ROWID = 'AAASQAAAEAAAAIkAAB'
  12.                 </USQL>
  13.                 <USQL exec="yes">
  14.                  update "HR"."T_CASCADE" set "ID" = '1' where "ID" = '11' and ROWID = 'AAASQAAAEAAAAIkAAA'
  15.                 </USQL>
  16.         </UNDO_SQL>
  17.         <DEPENDENT_XIDS>
  18.                 <TRANSACTION XID="0E0004002D000000">
  19.                 <CHARACTERISTICS>
  20.                 </CHARACTERISTICS>
  21.                 <UNDO_SQL>
  22.                         <USQL exec="yes">
  23.                          update "HR"."T_CASCADE" set "ID" = '33' where "ID" = '333' and ROWID = 'AAASQAAAEAAAAIkAAC'
  24.                         </USQL>
  25.                         <USQL exec="yes">
  26.                          update "HR"."T_CASCADE" set "ID" = '22' where "ID" = '222' and ROWID = 'AAASQAAAEAAAAIkAAB'
  27.                         </USQL>
  28.                 </UNDO_SQL>
  29.                 <DEPENDENT_XIDS>
  30.                         <TRANSACTION XID="110019002C000000">
  31.                         <CHARACTERISTICS>
  32.                         </CHARACTERISTICS>
  33.                         <UNDO_SQL>
  34.                                 <USQL exec="yes">
  35.                                  update "HR"."T_CASCADE" set "ID" = '222' where "ID" = '22' and ROWID = 'AAASQAAAEAAAAIkAAB'
  36.                                 </USQL>
  37.                         </UNDO_SQL>
  38.                         <DEPENDENT_XIDS>
  39.                         </DEPENDENT_XIDS>
  40.                         </TRANSACTION>
  41.                 </DEPENDENT_XIDS>
  42.                 </TRANSACTION>
  43.         </DEPENDENT_XIDS>
  44.         </TRANSACTION>
  45. <EXECUTED_UNDO_SQL>
  46. <EXEC_USQL>update "HR"."T_CASCADE" set "ID" = '222' where "ID" = '22' and ROWID = 'AAASQAAAEAAAAIkAAB'
  47. </EXEC_USQL>
  48. <EXEC_USQL>update "HR"."T_CASCADE" set "ID" = '33' where "ID" = '333' and ROWID = 'AAASQAAAEAAAAIkAAC'
  49. </EXEC_USQL>
  50. <EXEC_USQL>update "HR"."T_CASCADE" set "ID" = '22' where "ID" = '222' and ROWID = 'AAASQAAAEAAAAIkAAB'
  51. </EXEC_USQL>
  52. <EXEC_USQL>update "HR"."T_CASCADE" set "ID" = '3' where "ID" = '33' and ROWID = 'AAASQAAAEAAAAIkAAC'
  53. </EXEC_USQL>
  54. <EXEC_USQL>update "HR"."T_CASCADE" set "ID" = '2' where "ID" = '22' and ROWID = 'AAASQAAAEAAAAIkAAB'
  55. </EXEC_USQL>
  56. <EXEC_USQL>update "HR"."T_CASCADE" set "ID" = '1' where "ID" = '11' and ROWID = 'AAASQAAAEAAAAIkAAA'
  57. </EXEC_USQL>
  58. </EXECUTED_UNDO_SQL>
  59. </COMP_XID_REPORT>
复制代码
  1. select  *  from flashback_transaction_query
  2. where table_name='T_CASCADE'  and logon_user='HR'  order by 2 ;

  4. begin
  5.   dbms_flashback.transaction_backout(numtxns => 1,
  6.                                                                 xids =>  xid_array('13000D002D000000'),
  7.                                                                 options => dbms_flashback.cascade);
  8. end;

  10. select  * from hr.t_cascade;

  12. select  * from dba_flashback_txn_report;
  13.       
  14. commit;      
复制代码
NOCASCADE_FORCE：
报告：
  1. <?xml version="1.0" encoding="ISO-8859-1"?>
  2. <COMP_XID_REPORT XID="120014002E000000" NAME="_SYS_COMP_TXN_5112210_TIM_1522066588">
  3.         <TRANSACTION XID="0D0010002F000000">
  4.         <CHARACTERISTICS>
  5.         </CHARACTERISTICS>
  6.         <UNDO_SQL>
  7.                 <USQL exec="yes">
  8.                  update "HR"."T_NOCASCADE_FORCE" set "ID" = '3' where "ID" = '33' and ROWID = 'AAASQBAAEAAAAIsAAC'
  9.                 </USQL>
  10.                 <USQL exec="yes">
  11.                  update "HR"."T_NOCASCADE_FORCE" set "ID" = '2' where "ID" = '22' and ROWID = 'AAASQBAAEAAAAIsAAB'
  12.                 </USQL>
  13.                 <USQL exec="yes">
  14.                  update "HR"."T_NOCASCADE_FORCE" set "ID" = '1' where "ID" = '11' and ROWID = 'AAASQBAAEAAAAIsAAA'
  15.                 </USQL>
  16.         </UNDO_SQL>
  17.         <DEPENDENT_XIDS>
  18.                 <TRANSACTION XID="100001002E000000">
  19.                 <CHARACTERISTICS>
  20.                 </CHARACTERISTICS>
  21.                 <UNDO_SQL>
  22.                         <USQL exec="no">
  23.                          update "HR"."T_NOCASCADE_FORCE" set "ID" = '33' where "ID" = '333' and ROWID = 'AAASQBAAEAAAAIsAAC'
  24.                         </USQL>
  25.                         <USQL exec="no">
  26.                          update "HR"."T_NOCASCADE_FORCE" set "ID" = '22' where "ID" = '222' and ROWID = 'AAASQBAAEAAAAIsAAB'
  27.                         </USQL>
  28.                 </UNDO_SQL>
  29.                 <DEPENDENT_XIDS>
  30.                         <TRANSACTION XID="110018002D000000">
  31.                         <CHARACTERISTICS>
  32.                         </CHARACTERISTICS>
  33.                         <UNDO_SQL>
  34.                                 <USQL exec="no">
  35.                                  update "HR"."T_NOCASCADE_FORCE" set "ID" = '222' where "ID" = '22' and ROWID = 'AAASQBAAEAAAAIsAAB'
  36.                                 </USQL>
  37.                         </UNDO_SQL>
  38.                         <DEPENDENT_XIDS>
  39.                         </DEPENDENT_XIDS>
  40.                         </TRANSACTION>
  41.                 </DEPENDENT_XIDS>
  42.                 </TRANSACTION>
  43.         </DEPENDENT_XIDS>
  44.         </TRANSACTION>
  45. <EXECUTED_UNDO_SQL>
  46. <EXEC_USQL>update "HR"."T_NOCASCADE_FORCE" set "ID" = '3' where "ID" = '33' and ROWID = 'AAASQBAAEAAAAIsAAC'
  47. </EXEC_USQL>
  48. <EXEC_USQL>update "HR"."T_NOCASCADE_FORCE" set "ID" = '2' where "ID" = '22' and ROWID = 'AAASQBAAEAAAAIsAAB'
  49. </EXEC_USQL>
  50. <EXEC_USQL>update "HR"."T_NOCASCADE_FORCE" set "ID" = '1' where "ID" = '11' and ROWID = 'AAASQBAAEAAAAIsAAA'
  51. </EXEC_USQL>
  52. </EXECUTED_UNDO_SQL>
  53. </COMP_XID_REPORT>
复制代码
  1. select  *  from flashback_transaction_query
  2. where table_name='T_NOCASCADE_FORCE'  and logon_user='HR'  order by 2 ;

  4. begin
  5.   dbms_flashback.transaction_backout(numtxns => 1,
  6.                                                                 xids =>  xid_array('0D0010002F000000'),
  7.                                                                 options => dbms_flashback.nocascade_force);
  8. end;

  10. select  * from hr.t_nocascade_force;

  12. select  * from dba_flashback_txn_report;
  13.       
  14. commit;     
复制代码
NONCONFLICT_ONLY：
报告：
  1. <?xml version="1.0" encoding="ISO-8859-1"?>
  2. <COMP_XID_REPORT XID="0C0010002E000000" NAME="_SYS_COMP_TXN_5112210_TIM_1522067033">
  3.         <TRANSACTION XID="1400130032000000">
  4.         <CHARACTERISTICS>
  5.         </CHARACTERISTICS>
  6.         <UNDO_SQL>
  7.                 <USQL exec="no">
  8.                  update "HR"."T_NONCONFLICT_ONLY" set "ID" = '3' where "ID" = '33' and ROWID = 'AAASQCAAEAAAAI0AAC'
  9.                 </USQL>
  10.                 <USQL exec="no">
  11.                  update "HR"."T_NONCONFLICT_ONLY" set "ID" = '2' where "ID" = '22' and ROWID = 'AAASQCAAEAAAAI0AAB'
  12.                 </USQL>
  13.                 <USQL exec="yes">
  14.                  update "HR"."T_NONCONFLICT_ONLY" set "ID" = '1' where "ID" = '11' and ROWID = 'AAASQCAAEAAAAI0AAA'
  15.                 </USQL>
  16.         </UNDO_SQL>
  17.         <DEPENDENT_XIDS>
  18.                 <TRANSACTION XID="0D00170030000000">
  19.                 <CHARACTERISTICS>
  20.                 </CHARACTERISTICS>
  21.                 <UNDO_SQL>
  22.                         <USQL exec="no">
  23.                          update "HR"."T_NONCONFLICT_ONLY" set "ID" = '33' where "ID" = '333' and ROWID = 'AAASQCAAEAAAAI0AAC'
  24.                         </USQL>
  25.                         <USQL exec="no">
  26.                          update "HR"."T_NONCONFLICT_ONLY" set "ID" = '22' where "ID" = '222' and ROWID = 'AAASQCAAEAAAAI0AAB'
  27.                         </USQL>
  28.                 </UNDO_SQL>
  29.                 <DEPENDENT_XIDS>
  30.                         <TRANSACTION XID="0F001F0031000000">
  31.                         <CHARACTERISTICS>
  32.                         </CHARACTERISTICS>
  33.                         <UNDO_SQL>
  34.                                 <USQL exec="no">
  35.                                  update "HR"."T_NONCONFLICT_ONLY" set "ID" = '222' where "ID" = '22' and ROWID = 'AAASQCAAEAAAAI0AAB'
  36.                                 </USQL>
  37.                         </UNDO_SQL>
  38.                         <DEPENDENT_XIDS>
  39.                         </DEPENDENT_XIDS>
  40.                         </TRANSACTION>
  41.                 </DEPENDENT_XIDS>
  42.                 </TRANSACTION>
  43.         </DEPENDENT_XIDS>
  44.         </TRANSACTION>
  45. <EXECUTED_UNDO_SQL>
  46. <EXEC_USQL>update "HR"."T_NONCONFLICT_ONLY" set "ID" = '1' where "ID" = '11' and ROWID = 'AAASQCAAEAAAAI0AAA'
  47. </EXEC_USQL>
  48. </EXECUTED_UNDO_SQL>
  49. </COMP_XID_REPORT>
复制代码
  1. select  *  from flashback_transaction_query
  2. where table_name='T_NONCONFLICT_ONLY'  and logon_user='HR'  order by 2 ;

  4. begin
  5.   dbms_flashback.transaction_backout(numtxns => 1,
  6.                                                                 xids =>  xid_array('1400130032000000'),
  7.                                                                 options => dbms_flashback.nonconflict_only);
  8. end;

  10. select  * from hr.t_nonconflict_only;

  12. select  * from dba_flashback_txn_report;
  13.       
  14. commit;                                                   
复制代码

主键（插入-删除-再插入）：





  1. SQL> create table t05310_b ( a  number  constraint pk_t05310_b primary key  ) ;

  3. Table created.

  5. SQL> insert into t05310_b  values ( 1 )  ;  

  7. 1 row created.

  9. SQL> commit;

  11. Commit complete.

  13. SQL> delete from t05310_b where a=1;

  15. 1 row deleted.

  17. SQL> commit;

  19. Commit complete.

  21. SQL> insert into t05310_b  values (1) ;

  23. 1 row created.

  25. SQL> commit;

  27. Commit complete.

  29. SQL>
复制代码
用NOCASCADE_FORCE（只怕约束）：
Screenshot-Error.png

如果用NONCONFLICT_ONLY什么也没做：
  1. <?xml version="1.0" encoding="ISO-8859-1"?>
  2. <COMP_XID_REPORT XID="1000180030000000">
  3.         <TRANSACTION XID="12001A0030000000">
  4.         <CHARACTERISTICS>
  5.         </CHARACTERISTICS>
  6.         <UNDO_SQL>
  7.                 <USQL exec="no">
  8.                  insert into "HR"."T05310_B"("A") values ('1')
  9.                 </USQL>
  10.         </UNDO_SQL>
  11.         <DEPENDENT_XIDS>
  12.                 <TRANSACTION XID="1000090030000000">
  13.                 <CHARACTERISTICS>
  14.                 </CHARACTERISTICS>
  15.                 <UNDO_SQL>
  16.                         <USQL exec="no">
  17.                          delete from "HR"."T05310_B" where "A" = '1'
  18.                         </USQL>
  19.                 </UNDO_SQL>
  20.                 <DEPENDENT_XIDS>
  21.                 </DEPENDENT_XIDS>
  22.                 </TRANSACTION>
  23.         </DEPENDENT_XIDS>
  24.         </TRANSACTION>
  25. <EXECUTED_UNDO_SQL>
  26. </EXECUTED_UNDO_SQL>
  27. </COMP_XID_REPORT>
复制代码

外键：

  1. SQL> create table t05310_c ( a  number constraint pk_t05310_c primary key ) ;

  3. Table created.

  5. SQL> insert into t05310_c values ( 1 ) ;

  7. 1 row created.

  9. SQL> commit;

  11. Commit complete.

  13. SQL> create table t05310_d ( a number constraint fk_t05310_d references t05310_c ) ;

  15. Table created.

  17. SQL> update t05310_c set a=100 where a=1;

  19. 1 row updated.

  21. SQL> commit;

  23. Commit complete.

  25. SQL> insert into t05310_d values ( 1) ;
  26. insert into t05310_d values ( 1)
  27. *
  28. ERROR at line 1:
  29. ORA-02291: integrity constraint (HR.FK_T05310_D) violated - parent key not
  30. found


  33. SQL> insert into t05310_d values ( 100) ;

  35. 1 row created.

  37. SQL> commit;

  39. Commit complete.

  41. SQL>
复制代码

Screenshot-Error-1.png
以上错误是因为没有外键补充日志。
回复

使用道具 举报

返回列表 发新帖
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-4-19 19:32 , Processed in 0.089151 second(s), 27 queries .

快速回复 返回顶部 返回列表