课程第20次

botang

flatxn.zip (2.05 KB, 下载次数: 72)

2019-6-24 20:26 上传

点击文件名下载附件

1. select  * from flashback_transaction_query ftq
   
2. where ftq.logon_user='HR' and ftq.table_name='REGIONS'
   
3.   order by commit_scn;

复制代码

XID

START_SCN

START_TIMESTAMP

COMMIT_SCN

COMMIT_TIMESTAMP

LOGON_USER

UNDO_CHANGE#

OPERATION

TABLE_NAME

TABLE_OWNER

ROW_ID

UNDO_SQL

1

0E001C002C000000

1205271

2019/6/24 20:44:34

1205273

2019/6/24 20:44:34

HR

2

INSERT

REGIONS

HR

AAAVVkAAEAAAAFPAAD

delete from "HR"."REGIONS" where ROWID = 'AAAVVkAAEAAAAFPAAD';

2

0E001C002C000000

1205271

2019/6/24 20:44:34

1205273

2019/6/24 20:44:34

HR

3

INSERT

REGIONS

HR

AAAVVkAAEAAAAFPAAC

delete from "HR"."REGIONS" where ROWID = 'AAAVVkAAEAAAAFPAAC';

3

0E001C002C000000

1205271

2019/6/24 20:44:34

1205273

2019/6/24 20:44:34

HR

4

INSERT

REGIONS

HR

AAAVVkAAEAAAAFPAAB

delete from "HR"."REGIONS" where ROWID = 'AAAVVkAAEAAAAFPAAB';

4

0E001C002C000000

1205271

2019/6/24 20:44:34

1205273

2019/6/24 20:44:34

HR

5

INSERT

REGIONS

HR

AAAVVkAAEAAAAFPAAA

delete from "HR"."REGIONS" where ROWID = 'AAAVVkAAEAAAAFPAAA';

5

0E001C002C000000

1205271

2019/6/24 20:44:34

1205273

2019/6/24 20:44:34

HR

1

INSERT

REGIONS

HR

AAAVVkAAEAAAAFPAAE

delete from "HR"."REGIONS" where ROWID = 'AAAVVkAAEAAAAFPAAE';

6

14001F002B000000

1205281

2019/6/24 20:44:52

1205283

2019/6/24 20:44:52

HR

1

UPDATE

REGIONS

HR

AAAVVkAAEAAAAFPAAB

update "HR"."REGIONS" set "REGION_NAME" = 'Moon' where ROWID = 'AAAVVkAAEAAAAFPAAB';

7

14001F002B000000

1205281

2019/6/24 20:44:52

1205283

2019/6/24 20:44:52

HR

2

UPDATE

REGIONS

HR

AAAVVkAAEAAAAFPAAA

update "HR"."REGIONS" set "REGION_NAME" = 'Pole' where ROWID = 'AAAVVkAAEAAAAFPAAA';

8

12001A002C000000

1205308

2019/6/24 20:45:07

1205311

2019/6/24 20:45:07

HR

3

UPDATE

REGIONS

HR

AAAVVkAAEAAAAFPAAA

update "HR"."REGIONS" set "REGION_NAME" = 'Two Poles' where ROWID = 'AAAVVkAAEAAAAFPAAA';

9

12001A002C000000

1205308

2019/6/24 20:45:07

1205311

2019/6/24 20:45:07

HR

2

UPDATE

REGIONS

HR

AAAVVkAAEAAAAFPAAD

update "HR"."REGIONS" set "REGION_NAME" = 'Mars' where ROWID = 'AAAVVkAAEAAAAFPAAD';

10

12001A002C000000

1205308

2019/6/24 20:45:07

1205311

2019/6/24 20:45:07

HR

1

UPDATE

REGIONS

HR

AAAVVkAAEAAAAFPAAE

update "HR"."REGIONS" set "REGION_NAME" = 'Saturn' where ROWID = 'AAAVVkAAEAAAAFPAAE';

11

13000C0023000000

1205319

2019/6/24 20:45:28

1205320

2019/6/24 20:45:28

HR

1

UPDATE

REGIONS

HR

AAAVVkAAEAAAAFPAAC

update "HR"."REGIONS" set "REGION_NAME" = 'Venus' where ROWID = 'AAAVVkAAEAAAAFPAAC';

要back out 事务2：

1. select * from dba_flashback_txn_report;

复制代码

	COMPENSATING_XID	COMPENSATING_TXN_NAME	COMMIT_TIME	XID_REPORT	USERNAME
1	1000140026000000	_SYS_COMP_TXN_1966309_TIM_1561381613	2019/6/24 21:18:22	<CLOB>	SYS

1. <?xml version="1.0" encoding="ISO-8859-1"?>
   
2. <COMP_XID_REPORT XID="1000140026000000" NAME="_SYS_COMP_TXN_1966309_TIM_1561381613">
   
3.         <TRANSACTION XID="14001F002B000000">
   
4.         <CHARACTERISTICS>
   
5.         </CHARACTERISTICS>
   
6.         <UNDO_SQL>
   
7.                 <USQL exec="yes">
   
8.                  update "HR"."REGIONS" set "REGION_NAME" = 'Moon' where "REGION_ID" = '20' and "REGION_NAME" = 'Many Moons' and ROWID = 'AAAVVkAAEAAAAFPAAB'
   
9.                 </USQL>
   
10.                 <USQL exec="no">
    
11.                  update "HR"."REGIONS" set "REGION_NAME" = 'Pole' where "REGION_ID" = '10' and "REGION_NAME" = 'Two Poles' and ROWID = 'AAAVVkAAEAAAAFPAAA'
    
12.                 </USQL>
    
13.         </UNDO_SQL>
    
14.         <DEPENDENT_XIDS>
    
15.                 <TRANSACTION XID="12001A002C000000">
    
16.                 <CHARACTERISTICS>
    
17.                 </CHARACTERISTICS>
    
18.                 <UNDO_SQL>
    
19.                         <USQL exec="no">
    
20.                          update "HR"."REGIONS" set "REGION_NAME" = 'Saturn' where "REGION_ID" = '50' and "REGION_NAME" = 'Big star' and ROWID = 'AAAVVkAAEAAAAFPAAE'
    
21.                         </USQL>
    
22.                         <USQL exec="no">
    
23.                          update "HR"."REGIONS" set "REGION_NAME" = 'Mars' where "REGION_ID" = '40' and "REGION_NAME" = 'Red star' and ROWID = 'AAAVVkAAEAAAAFPAAD'
    
24.                         </USQL>
    
25.                         <USQL exec="no">
    
26.                          update "HR"."REGIONS" set "REGION_NAME" = 'Two Poles' where "REGION_ID" = '10' and "REGION_NAME" = 'No star' and ROWID = 'AAAVVkAAEAAAAFPAAA'
    
27.                         </USQL>
    
28.                 </UNDO_SQL>
    
29.                 <DEPENDENT_XIDS>
    
30.                 </DEPENDENT_XIDS>
    
31.                 </TRANSACTION>
    
32.         </DEPENDENT_XIDS>
    
33.         </TRANSACTION>
    
34. <EXECUTED_UNDO_SQL>
    
35. <EXEC_USQL>update "HR"."REGIONS" set "REGION_NAME" = 'Moon' where "REGION_ID" = '20' and "REGION_NAME" = 'Many Moons' and ROWID = 'AAAVVkAAEAAAAFPAAB'
    
36. </EXEC_USQL>
    
37. </EXECUTED_UNDO_SQL>
    
38. </COMP_XID_REPORT>

复制代码

t05310.sql (388 Bytes, 下载次数: 75)

2019-6-24 21:45 上传

点击文件名下载附件

1. select  * from flashback_transaction_query ftq
   
2. where ftq.logon_user='HR' and ftq.table_name='T_CASCADE'
   
3.   order by commit_scn;
   
4. 
   
5. 
   
6. begin
   
7.   dbms_flashback.transaction_backout(numtxns => 1,
   
8.   xids => xid_array('0E001D0035000000'),
   
9.   options => dbms_flashback.cascade);
   
10. end;
    
11.   
    
12. 
    
13. select * from dba_flashback_txn_report;

复制代码

1. <?xml version="1.0" encoding="ISO-8859-1"?>
   
2. <COMP_XID_REPORT XID="0E0017003E000000">
   
3.         <TRANSACTION XID="0E001D0035000000">
   
4.         <CHARACTERISTICS>
   
5.         </CHARACTERISTICS>
   
6.         <UNDO_SQL>
   
7.                 <USQL exec="yes">
   
8.                  update "HR"."T_CASCADE" set "ID" = '3' where "ID" = '33' and ROWID = 'AAAVVrAAEAAAAFfAAC'
   
9.                 </USQL>
   
10.                 <USQL exec="yes">
    
11.                  update "HR"."T_CASCADE" set "ID" = '2' where "ID" = '22' and ROWID = 'AAAVVrAAEAAAAFfAAB'
    
12.                 </USQL>
    
13.                 <USQL exec="yes">
    
14.                  update "HR"."T_CASCADE" set "ID" = '1' where "ID" = '11' and ROWID = 'AAAVVrAAEAAAAFfAAA'
    
15.                 </USQL>
    
16.         </UNDO_SQL>
    
17.         <DEPENDENT_XIDS>
    
18.                 <TRANSACTION XID="1100010036000000">
    
19.                 <CHARACTERISTICS>
    
20.                 </CHARACTERISTICS>
    
21.                 <UNDO_SQL>
    
22.                         <USQL exec="yes">
    
23.                          update "HR"."T_CASCADE" set "ID" = '33' where "ID" = '333' and ROWID = 'AAAVVrAAEAAAAFfAAC'
    
24.                         </USQL>
    
25.                         <USQL exec="yes">
    
26.                          update "HR"."T_CASCADE" set "ID" = '22' where "ID" = '222' and ROWID = 'AAAVVrAAEAAAAFfAAB'
    
27.                         </USQL>
    
28.                 </UNDO_SQL>
    
29.                 <DEPENDENT_XIDS>
    
30.                         <TRANSACTION XID="1200100035000000">
    
31.                         <CHARACTERISTICS>
    
32.                         </CHARACTERISTICS>
    
33.                         <UNDO_SQL>
    
34.                                 <USQL exec="yes">
    
35.                                  update "HR"."T_CASCADE" set "ID" = '222' where "ID" = '22' and ROWID = 'AAAVVrAAEAAAAFfAAB'
    
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 = 'AAAVVrAAEAAAAFfAAB'
    
47. </EXEC_USQL>
    
48. <EXEC_USQL>update "HR"."T_CASCADE" set "ID" = '33' where "ID" = '333' and ROWID = 'AAAVVrAAEAAAAFfAAC'
    
49. </EXEC_USQL>
    
50. <EXEC_USQL>update "HR"."T_CASCADE" set "ID" = '22' where "ID" = '222' and ROWID = 'AAAVVrAAEAAAAFfAAB'
    
51. </EXEC_USQL>
    
52. <EXEC_USQL>update "HR"."T_CASCADE" set "ID" = '3' where "ID" = '33' and ROWID = 'AAAVVrAAEAAAAFfAAC'
    
53. </EXEC_USQL>
    
54. <EXEC_USQL>update "HR"."T_CASCADE" set "ID" = '2' where "ID" = '22' and ROWID = 'AAAVVrAAEAAAAFfAAB'
    
55. </EXEC_USQL>
    
56. <EXEC_USQL>update "HR"."T_CASCADE" set "ID" = '1' where "ID" = '11' and ROWID = 'AAAVVrAAEAAAAFfAAA'
    
57. </EXEC_USQL>
    
58. </EXECUTED_UNDO_SQL>
    
59. </COMP_XID_REPORT>

复制代码