课程第56次（2017-09-22星期五）

botang

第三阶段New Feature/OCM Exam Preparation（31-56）

1. select  ftq.xid, ftq.table_name, ftq.operation, ftq.undo_sql
   
2. from flashback_transaction_query ftq
   
3. where ftq.logon_user='HR'
   
4. order by ftq.start_scn desc;
   
5. 
   
6.   begin
   
7.      dbms_flashback.transaction_backout (
   
8.      numtxns => 1,
   
9.      xids =>  xid_array('03001500D3050000'),
   
10.      options => dbms_flashback.nocascade);
    
11.    end;
    
12.      
    
13.    alter database add supplemental log data;
    
14.    
    
15.    select  d.SUPPLEMENTAL_LOG_DATA_MIN,
    
16.               d.SUPPLEMENTAL_LOG_DATA_PK,
    
17.               d.SUPPLEMENTAL_LOG_DATA_FK
    
18.     from v_$database d;
    
19.      
    
20.     ----
    
21.     select  ftq.xid, ftq.table_name, ftq.operation, ftq.undo_sql
    
22. from flashback_transaction_query ftq
    
23. where ftq.logon_user='HR'  and ftq.table_name='T_NOCASCADE2'
    
24. order by ftq.start_scn desc;
    
25. 
    
26. ---0100180051050000
    
27. begin
    
28.      dbms_flashback.transaction_backout (
    
29.      numtxns => 1,
    
30.      xids =>  xid_array('0100180051050000'),
    
31.      options => dbms_flashback.nocascade);
    
32.    end;
    
33. ---
    
34. select  ftq.xid, ftq.table_name, ftq.operation, ftq.undo_sql
    
35. from flashback_transaction_query ftq
    
36. where ftq.logon_user='HR'  and ftq.table_name='T_CASCADE'
    
37. order by ftq.start_scn desc;
    
38. 
    
39. ---09000700F1050000  ---0A000D00FD040000
    
40. begin
    
41.      dbms_flashback.transaction_backout (
    
42.      numtxns => 1,
    
43.      xids =>  xid_array('09000700F1050000'),
    
44.      options => dbms_flashback.cascade);
    
45.    end;
    
46.    ---
    
47.    select  * from dba_flashback_txn_report;
    
48.    
    
49.    select  * from dba_flashback_txn_state;
    
50.    commit;
    
51.    
    
52.    ----
    
53.    select  ftq.xid, ftq.table_name, ftq.operation, ftq.undo_sql
    
54. from flashback_transaction_query ftq
    
55. where ftq.logon_user='HR'  and ftq.table_name='T_NOCASCADE_FORCE'
    
56. order by ftq.start_scn desc;
    
57. 
    
58. ---0700130049050000  ---04001A0057050000
    
59. begin
    
60.      dbms_flashback.transaction_backout (
    
61.      numtxns => 1,
    
62.      xids =>  xid_array('0700130049050000'),
    
63.      options => dbms_flashback.nocascade_force);
    
64.    end;
    
65.    ---
    
66.    select  * from dba_flashback_txn_report;
    
67.    
    
68.    select  * from dba_flashback_txn_state;
    
69.    commit;
    
70.    
    
71.    ----
    
72.     ----
    
73.    select  ftq.xid, ftq.table_name, ftq.operation, ftq.undo_sql
    
74. from flashback_transaction_query ftq
    
75. where ftq.logon_user='HR'  and ftq.table_name='T_NONCONFLICT_ONLY'
    
76. order by ftq.start_scn desc;
    
77. 
    
78. ---02001900F1050000  ---04001A0057050000
    
79. begin
    
80.      dbms_flashback.transaction_backout (
    
81.      numtxns => 1,
    
82.      xids =>  xid_array('02001900F1050000'),
    
83.      options => dbms_flashback.nonconflict_only);
    
84.    end;
    
85.    ---
    
86.    select  * from dba_flashback_txn_report;
    
87.    
    
88.    select  * from dba_flashback_txn_state;
    
89.    commit;
    
90.    ---
    
91.    select  ftq.xid, ftq.table_name, ftq.operation, ftq.undo_sql
    
92. from flashback_transaction_query ftq
    
93. where ftq.logon_user='HR'  and ftq.table_name='S_NOCASCADE_FORCE'
    
94. order by ftq.start_scn desc;
    
95. 
    
96. ---02001F00F4050000
    
97. 
    
98. begin
    
99.      dbms_flashback.transaction_backout (
    
100.      numtxns => 1,
     
101.      xids =>  xid_array('02001F00F4050000'),
     
102.      options => dbms_flashback.nocascade_force
     
103.      
     
104.      );
     
105.    end;
     
106.       select  * from dba_flashback_txn_report;
     
107.    commit;
     
108.    

复制代码

1. SQL> select  * from t_nocascade;
   
2. 
   
3.         ID
   
4. ----------
   
5.         11
   
6.         22
   
7.        333
   
8. 
   
9. SQL> select  * from t_cascade;
   
10. 
    
11.         ID
    
12. ----------
    
13.          1
    
14.          2
    
15.          3
    
16. 
    
17. SQL> select  * from t_nocascade_force;
    
18. 
    
19.         ID
    
20. ----------
    
21.          1
    
22.          2
    
23.        333
    
24. 
    
25. SQL> select  * from t_nonconflict_only;
    
26. 
    
27.         ID
    
28. ----------
    
29.          1
    
30.         22
    
31.        333
    

复制代码

1. SQL> select  * from s_nocascade;
   
2. 
   
3.         ID
   
4. ----------
   
5.         11
   
6.        222
   
7.        333
   
8. 
   
9. 
   
10. SQL> select  * from s_cascade;
    
11. 
    
12.         ID
    
13. ----------
    
14.          1
    
15.          2
    
16.          3
    
17. 
    
18. SQL> select  * from s_nocascade_force;
    
19. 
    
20.         ID
    
21. ----------
    
22.          1
    
23.        222
    
24.        333
    
25. 
    
26. 
    
27. SQL> select  * from s_nonconflict_only;
    
28. 
    
29.         ID
    
30. ----------
    
31.          1
    
32.        222
    
33.        333
    

复制代码

1. SQL> create table t_pk ( a  number primary key  )  ;
   
2. 
   
3. Table created.
   
4. 
   
5. SQL> insert into t_pk values (1) ;
   
6. 
   
7. 1 row created.
   
8. 
   
9. SQL> commit;
   
10. 
    
11. Commit complete.
    
12. 
    
13. SQL> delete from t_pk  where a=1;
    
14. 
    
15. 1 row deleted.
    
16. 
    
17. SQL> commit;
    
18. 
    
19. Commit complete.
    
20. 
    
21. SQL> insert into t_pk values (1) ;
    
22. 
    
23. 1 row created.
    
24. 
    
25. SQL> commit;
    
26. 
    
27. Commit complete.
    
28. 
    
29. SQL> select  * from t_pk;
    
30. 
    
31.          A
    
32. ----------
    
33.          1
    
34. 
    
35. SQL>
    

复制代码

1. SQL>  create table t_pk2 ( a  number primary key  )  ;
   
2. 
   
3. Table created.
   
4. 
   
5. SQL> insert into t_pk2 values (1) ;
   
6. 
   
7. 1 row created.
   
8. 
   
9. SQL> commit;
   
10. 
    
11. Commit complete.
    
12. 
    
13. SQL>
    
14. SQL> delete from t_pk2 where a=1;
    
15. 
    
16. 1 row deleted.
    
17. 
    
18. SQL> commit;
    
19. 
    
20. Commit complete.
    
21. 
    
22. SQL> insert into t_pk2  values (1) ;
    
23. 
    
24. 1 row created.
    
25. 
    
26. SQL> commit;
    
27. 
    
28. Commit complete.
    
29. 
    
30. SQL> select  * from t_pk2;
    
31. 
    
32.          A
    
33. ----------
    
34.          1
    

复制代码

1. SQL>  create table t_pk3 ( a  number primary key  )  ;
   
2. 
   
3. Table created.
   
4. 
   
5. SQL> insert into t_pk3  values (1) ;
   
6. 
   
7. 1 row created.
   
8. 
   
9. SQL> commit;
   
10. 
    
11. Commit complete.
    
12. 
    
13. SQL> create table t_pk3_son ( a  number refreneces t_pk3 ) ;
    
14. create table t_pk3_son ( a  number refreneces t_pk3 )
    
15.                                    *
    
16. ERROR at line 1:
    
17. ORA-00907: missing right parenthesis
    
18. 
    
19. 
    
20. SQL> create table t_pk3_son ( a  number references t_pk3 ) ;
    
21. 
    
22. Table created.
    
23. 
    
24. SQL> insert into t_pk3_son values (2) ;
    
25. insert into t_pk3_son values (2)
    
26. *
    
27. ERROR at line 1:
    
28. ORA-02291: integrity constraint (HR.SYS_C0014932) violated - parent key not
    
29. found
    
30. 
    
31. 
    
32. SQL> insert into t_pk3_son values (1) ;
    
33. 
    
34. 1 row created.
    
35. 
    
36. SQL> commit;
    
37. 
    
38. Commit complete.
    
39. 
    
40. SQL>
    

复制代码

1. select  ftq.xid, ftq.table_name, ftq.operation, ftq.undo_sql
   
2. from flashback_transaction_query ftq
   
3. where ftq.logon_user='HR'  and ftq.table_name='T_PK4'
   
4. order by ftq.start_scn desc;
   
5. 
   
6. ---04000A005E050000
   
7. 
   
8. begin
   
9.      dbms_flashback.transaction_backout (
   
10.      numtxns => 1,
    
11.      xids =>  xid_array('02000A00F5050000'),
    
12.      options => dbms_flashback.cascade   
    
13.      );
    
14.    end;
    
15.    
    
16.       select  * from dba_flashback_txn_report;
    
17.    commit;
    
18.    ---
    
19.     select  d.SUPPLEMENTAL_LOG_DATA_MIN,
    
20.               d.SUPPLEMENTAL_LOG_DATA_PK,
    
21.               d.SUPPLEMENTAL_LOG_DATA_FK
    
22.     from v_$database d;
    
23.    
    
24.     ---
    
25.     alter database add supplemental log data ( foreign key  ) columns;
    
26.    

复制代码

<?xml version="1.0" encoding="ISO-8859-1"?>
<COMP_XID_REPORT XID="02000400D2050000" NAME="_SYS_COMP_TXN_4456479_TIM_1506087917">
    <TRANSACTION XID="02000A00F5050000">
    <CHARACTERISTICS>
    </CHARACTERISTICS>
    <UNDO_SQL>
        <USQL exec="yes">
         delete from "HR"."T_PK4" where "A" = '1'
        </USQL>
    </UNDO_SQL>
    <DEPENDENT_XIDS>
        <TRANSACTION XID="0800070067050000">
        <CHARACTERISTICS>
        </CHARACTERISTICS>
        <UNDO_SQL>
            <USQL exec="yes">
             delete from "HR"."T_PK4_SON" where "A" = '1' and ROWID = 'AAATrpAAEAAAAKcAAA'
            </USQL>
        </UNDO_SQL>
        <DEPENDENT_XIDS>
        </DEPENDENT_XIDS>
        </TRANSACTION>
    </DEPENDENT_XIDS>
    </TRANSACTION>
<EXECUTED_UNDO_SQL>
<EXEC_USQL>delete from "HR"."T_PK4_SON" where "A" = '1' and ROWID = 'AAATrpAAEAAAAKcAAA'
</EXEC_USQL>
<EXEC_USQL>delete from "HR"."T_PK4" where "A" = '1'
</EXEC_USQL>
</EXECUTED_UNDO_SQL>
</COMP_XID_REPORT>

1. SQL> select  * from t_pk4_son;
   
2. 
   
3. no rows selected
   
4. 
   
5. SQL> select  * from t_pk4;
   
6. 
   
7. no rows selected
   
8. 
   

复制代码