undo transaction slot被覆蓋引起ORA-01555的原理解析
測試環境:oracle 11gR2 Restart
測試目的:模擬undo header事務表槽被覆蓋引起ORA-01555的現象及原理解析
場景介紹:
Session 1:
獲取scott.tabnow1表中記錄對應的relative_fno和block_number;
Update一條記錄,但不commit,記錄下所使用的xid、uba資訊
Session 2:
以非SYS使用者連線執行:set transaction read only;
Session 1:
Commit;
Session 3:
對scott.t1表連續做update+commit操作
Session 2:
成功遍歷scott.tabnow1表
select * from scott.tabnow1;
Session 4:
再次對scott.t1表連續做update+commit操作,繼續覆蓋事務表
Session 2:
再次遍歷scott.tabnow1表
得到ORA-01555錯誤
測試資料庫配置:
##為簡化測試過程,建立了一個256K的undotbs
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOSIG
SQL> select d.name,d.bytes from v$datafile d,v$tablespace t where t.ts#=d.ts# and t.name='UNDOSIG';
NAME BYTES
---------------------------------------- ----------
+STESTDG1/stest2/undosig.dbf 262144
##undosig裡只有一個undo segment online
SQL> select segment_name,tablespace_name,relative_fno,status from dba_rollback_segs where tablespace_name='UNDOSIG';
SEGMENT_NAME TABLESPACE_NAME RELATIVE_FNO STATUS
------------------------------ ------------------------------ ------------ ----------------
_SYSSMU2$ UNDOSIG 8 ONLINE
##undo segment裡extent的分佈情況,除去undo header所佔據的block 8,block 9~31都可以被事務用來存放修改前內容:
SQL> select segment_name,extent_id,file_id,block_id,blocks from dba_extents where segment_name='_SYSSMU2$';
SEGMENT_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS
------------------------------ ---------- ---------- ---------- ----------
_SYSSMU2$ 0 8 8 8
_SYSSMU2$ 1 8 16 8
_SYSSMU2$ 2 8 24 8
##測試用到的兩張表初始內容:
SQL> select * from scott.tabnow1;
USERNAME USER_ID CREATED
------------------------------ ---------- ------------
XS$NULLLL 2147483638 21-OCT-11
NEWUSER 84 12-MAR-14
SCOTTTTT 83 21-OCT-11
OWBSYS_AUDIT 82 21-OCT-11
OWBSYS 78 21-OCT-11
APEX 77 21-OCT-11
APEX_PUBLIC 75 21-OCT-11
FLOWS_FILE 74 21-OCT-11
MGMT_VIEW 73 21-OCT-11
DDD 34 28-MAY-14
SQL> select * from scott.t1;
ID
----------
34
34
開始測試過程:
/////////////
//session 1:
/////////////
##確定scott.tabnow1表中的記錄所在的塊,便於之後對data block作dump;所有的行都在同一個block中
select dbms_rowid.rowid_relative_fno(rowid) rfno,dbms_rowid.rowid_block_number(rowid) blkno from scott.tabnow1;
RFNO BLKNO
---------- ----------
4 1779
4 1779
4 1779
4 1779
4 1779
4 1779
4 1779
4 1779
4 1779
4 1779
##記錄一下undo header的初始狀態,slot 0x1e將成為下一個要用到的slot
TRN CTL:: seq: 0x187e chd: 0x001e ctl: 0x0000 inc: 0x00000000 nfb: 0x0001
mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x02000010.187e.18 scn: 0x0000.00ae9095
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x00 9 0x00 0x11d2 0xffff 0x0000.00ae9241 0x02000010 0x0000.000.00000000 0x00000001 0x00000000 1402531961
0x01 9 0x00 0x11cf 0x001a 0x0000.00ae90f1 0x0200000b 0x0000.000.00000000 0x00000003 0x00000000 1402531591
0x02 9 0x00 0x11d2 0x0016 0x0000.00ae9199 0x0200001c 0x0000.000.00000000 0x00000003 0x00000000 1402531591
0x03 9 0x00 0x11d4 0x0007 0x0000.00ae91f4 0x02000010 0x0000.000.00000000 0x00000001 0x00000000 1402531756
0x04 9 0x00 0x11cd 0x0006 0x0000.00ae90af 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1402531591
0x05 9 0x00 0x11d0 0x000e 0x0000.00ae915a 0x02000017 0x0000.000.00000000 0x00000003 0x00000000 1402531591
0x06 9 0x00 0x11d2 0x0019 0x0000.00ae90b2 0x0200001f 0x0000.000.00000000 0x00000001 0x00000000 1402531591
0x07 9 0x00 0x11d3 0x000d 0x0000.00ae91f6 0x02000010 0x0000.000.00000000 0x00000001 0x00000000 1402531756
0x08 9 0x00 0x11d3 0x0014 0x0000.00ae911b 0x0200001b 0x0000.000.00000000 0x00000003 0x00000000 1402531591
0x09 9 0x00 0x11d2 0x000a 0x0000.00ae916f 0x0200000c 0x0000.000.00000000 0x00000003 0x00000000 1402531591
0x0a 9 0x00 0x11d3 0x0012 0x0000.00ae9179 0x0200000e 0x0000.000.00000000 0x00000003 0x00000000 1402531591
0x0b 9 0x00 0x11d0 0x0017 0x0000.00ae90d2 0x02000015 0x0000.000.00000000 0x00000003 0x00000000 1402531591
0x0c 9 0x00 0x11ce 0x001b 0x0000.00ae9145 0x02000013 0x0000.000.00000000 0x00000003 0x00000000 1402531591
0x0d 9 0x00 0x11d2 0x0000 0x0000.00ae91f8 0x02000010 0x0000.000.00000000 0x00000001 0x00000000 1402531756
0x0e 9 0x00 0x11d1 0x0009 0x0000.00ae9164 0x0200000a 0x0000.000.00000000 0x00000003 0x00000000 1402531591
0x0f 9 0x00 0x11d0 0x0003 0x0000.00ae91ae 0x02000010 0x0000.000.00000000 0x00000003 0x00000000 1402531591
0x10 9 0x00 0x11d3 0x000b 0x0000.00ae90c7 0x02000013 0x0000.000.00000000 0x00000003 0x00000000 1402531591
0x11 9 0x00 0x11d4 0x0008 0x0000.00ae9111 0x02000019 0x0000.000.00000000 0x00000003 0x00000000 1402531591
0x12 9 0x00 0x11d0 0x001f 0x0000.00ae9184 0x02000018 0x0000.000.00000000 0x00000003 0x00000000 1402531591
0x13 9 0x00 0x11cd 0x001c 0x0000.00ae909d 0x0200001d 0x0000.000.00000000 0x00000001 0x00000000 1402531591
0x14 9 0x00 0x11d1 0x0015 0x0000.00ae9126 0x0200001d 0x0000.000.00000000 0x00000003 0x00000000 1402531591
0x15 9 0x00 0x11d1 0x0020 0x0000.00ae9130 0x0200001f 0x0000.000.00000000 0x00000003 0x00000000 1402531591
0x16 9 0x00 0x11d1 0x000f 0x0000.00ae91a3 0x0200001e 0x0000.000.00000000 0x00000003 0x00000000 1402531591
0x17 9 0x00 0x11d1 0x0021 0x0000.00ae90dc 0x02000016 0x0000.000.00000000 0x00000002 0x00000000 1402531591
0x18 9 0x00 0x11d0 0x0011 0x0000.00ae9106 0x0200000f 0x0000.000.00000000 0x00000003 0x00000000 1402531591
0x19 9 0x00 0x11d2 0x0010 0x0000.00ae90bd 0x02000011 0x0000.000.00000000 0x00000003 0x00000000 1402531591
0x1a 9 0x00 0x11d1 0x0018 0x0000.00ae90fc 0x0200000d 0x0000.000.00000000 0x00000003 0x00000000 1402531591
0x1b 9 0x00 0x11d3 0x0005 0x0000.00ae914f 0x02000015 0x0000.000.00000000 0x00000003 0x00000000 1402531591
0x1c 9 0x00 0x11d0 0x001d 0x0000.00ae90a3 0x0200001d 0x0000.000.00000000 0x00000001 0x00000000 1402531591
0x1d 9 0x00 0x11d1 0x0004 0x0000.00ae90ad 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1402531591
0x1e 9 0x00 0x11d1 0x0013 0x0000.00ae909b 0x0200001c 0x0000.000.00000000 0x00000001 0x00000000 1402531591
0x1f 9 0x00 0x11d0 0x0002 0x0000.00ae918e 0x0200001a 0x0000.000.00000000 0x00000003 0x00000000 1402531591
0x20 9 0x00 0x11d2 0x000c 0x0000.00ae913b 0x02000011 0x0000.000.00000000 0x00000003 0x00000000 1402531591
0x21 9 0x00 0x11d0 0x0001 0x0000.00ae90e7 0x02000009 0x0000.000.00000000 0x00000002 0x00000000 1402531591
update scott.tabnow1 set username='XS$NULL' where user_id=2147483638;
select XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN,to_char(START_SCNB,'xxxxxxxx') start_scn from v$transaction;
XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBAREC UBASQN START_SCN
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------
2 30 4562 8 16 25 6270 ae9241
alter system flush buffer_cache;
alter system dump datafile 8 block 16;
---data block dump的結果中摘錄了Rec#0x19內容:
*-----------------------------
* Rec #0x19 slt: 0x1e objn: 90344(0x000160e8) objd: 90344 tblspc: 8(0x00000008)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000Ext idx: 0
flg2: 0
*-----------------------------
uba: 0x02000010.187e.18 ctl max scn: 0x0000.00ae9095 prv tx scn: 0x0000.00ae909b
txn start scn: scn: 0x0000.00ae90b2 logon user: 0
prev brb: 33554460 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: L itl: xid: 0x0002.004.0000111f uba: 0x0200001a.177b.09
flg: C--- lkc: 0 scn: 0x0000.00ad9579
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x010006f3 hdba: 0x010006f2
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
ncol: 3 nnew: 1 size: 2
col 0: [ 9] 58 53 24 4e 55 4c 4c 4c 4c
--將58 53 24 4e 55 4c 4c 4c 4c轉換為varchar2,得到修改前的複製
select utl_raw.cast_to_varchar2(replace('58 53 24 4e 55 4c 4c 4c 4c',' ')) from dual;
UTL_RAW.CAST_TO_VARCHAR2(REPLACE('5853244E554C4C4C4C',''))
--------------------------------------------------------------------------------
XS$NULLLL
alter system dump undo header "_SYSSMU2$";
--下面是undo header中有關事務控制和事務表的dump內容,slot 0x1e處於活動狀態,0x02000010即8/16
TRN CTL:: seq: 0x187e chd: 0x0009 ctl: 0x000e inc: 0x00000000 nfb: 0x0001
mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x02000014.187e.13 scn: 0x0000.00ae92e6
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x00 9 0x00 0x11d4 0x0013 0x0000.00ae92f6 0x02000013 0x0000.000.00000000 0x00000001 0x00000000 1402532191
0x01 9 0x00 0x11d2 0x001a 0x0000.00ae9303 0x02000013 0x0000.000.00000000 0x00000001 0x00000000 1402532191
0x02 9 0x00 0x11d4 0x0016 0x0000.00ae92ed 0x02000013 0x0000.000.00000000 0x00000001 0x00000000 1402532191
0x03 9 0x00 0x11d6 0x0007 0x0000.00ae92f2 0x02000013 0x0000.000.00000000 0x00000001 0x00000000 1402532191
0x04 9 0x00 0x11d0 0x0006 0x0000.00ae92fa 0x02000013 0x0000.000.00000000 0x00000001 0x00000000 1402532191
0x05 9 0x00 0x11d3 0x000e 0x0000.00ae9310 0x02000014 0x0000.000.00000000 0x00000001 0x00000000 1402532191
0x06 9 0x00 0x11d5 0x0019 0x0000.00ae92fc 0x02000013 0x0000.000.00000000 0x00000001 0x00000000 1402532191
0x07 9 0x00 0x11d5 0x000d 0x0000.00ae92f4 0x02000013 0x0000.000.00000000 0x00000001 0x00000000 1402532191
0x08 9 0x00 0x11d6 0x0014 0x0000.00ae9308 0x02000014 0x0000.000.00000000 0x00000001 0x00000000 1402532191
0x09 9 0x00 0x11d4 0x000a 0x0000.00ae92e7 0x02000013 0x0000.000.00000000 0x00000001 0x00000000 1402532191
0x0a 9 0x00 0x11d5 0x0012 0x0000.00ae92e9 0x02000013 0x0000.000.00000000 0x00000001 0x00000000 1402532191
0x0b 9 0x00 0x11d3 0x0017 0x0000.00ae9300 0x02000013 0x0000.000.00000000 0x00000001 0x00000000 1402532191
0x0c 9 0x00 0x11d1 0x001b 0x0000.00ae930e 0x02000014 0x0000.000.00000000 0x00000001 0x00000000 1402532191
0x0d 9 0x00 0x11d4 0x0000 0x0000.00ae92f5 0x02000013 0x0000.000.00000000 0x00000001 0x00000000 1402532191
0x0e 9 0x00 0x11d4 0xffff 0x0000.00ae9311 0x02000014 0x0000.000.00000000 0x00000001 0x00000000 1402532191
0x0f 9 0x00 0x11d2 0x0003 0x0000.00ae92f0 0x02000013 0x0000.000.00000000 0x00000001 0x00000000 1402532191
0x10 9 0x00 0x11d6 0x000b 0x0000.00ae92ff 0x02000013 0x0000.000.00000000 0x00000001 0x00000000 1402532191
0x11 9 0x00 0x11d7 0x0008 0x0000.00ae9307 0x02000014 0x0000.000.00000000 0x00000001 0x00000000 1402532191
0x12 9 0x00 0x11d2 0x001f 0x0000.00ae92eb 0x02000013 0x0000.000.00000000 0x00000001 0x00000000 1402532191
0x13 9 0x00 0x11d0 0x001c 0x0000.00ae92f7 0x02000013 0x0000.000.00000000 0x00000001 0x00000000 1402532191
0x14 9 0x00 0x11d4 0x0015 0x0000.00ae930a 0x02000014 0x0000.000.00000000 0x00000001 0x00000000 1402532191
0x15 9 0x00 0x11d4 0x0020 0x0000.00ae930b 0x02000014 0x0000.000.00000000 0x00000001 0x00000000 1402532191
0x16 9 0x00 0x11d3 0x000f 0x0000.00ae92ee 0x02000013 0x0000.000.00000000 0x00000001 0x00000000 1402532191
0x17 9 0x00 0x11d4 0x0021 0x0000.00ae9301 0x02000013 0x0000.000.00000000 0x00000001 0x00000000 1402532191
0x18 9 0x00 0x11d3 0x0011 0x0000.00ae9306 0x02000014 0x0000.000.00000000 0x00000001 0x00000000 1402532191
0x19 9 0x00 0x11d5 0x0010 0x0000.00ae92fe 0x02000013 0x0000.000.00000000 0x00000001 0x00000000 1402532191
0x1a 9 0x00 0x11d4 0x0018 0x0000.00ae9304 0x02000013 0x0000.000.00000000 0x00000001 0x00000000 1402532191
0x1b 9 0x00 0x11d6 0x0005 0x0000.00ae930f 0x02000014 0x0000.000.00000000 0x00000001 0x00000000 1402532191
0x1c 9 0x00 0x11d3 0x001d 0x0000.00ae92f8 0x02000013 0x0000.000.00000000 0x00000001 0x00000000 1402532191
0x1d 9 0x00 0x11d4 0x0004 0x0000.00ae92f9 0x02000013 0x0000.000.00000000 0x00000001 0x00000000 1402532191
0x1e 10 0x80 0x11d2 0x0002 0x0000.00ae9241 0x02000010 0x0000.000.00000000 0x00000001 0x00000000 0
0x1f 9 0x00 0x11d2 0x0002 0x0000.00ae92ec 0x02000013 0x0000.000.00000000 0x00000001 0x00000000 1402532191
0x20 9 0x00 0x11d5 0x000c 0x0000.00ae930c 0x02000014 0x0000.000.00000000 0x00000001 0x00000000 1402532191
0x21 9 0x00 0x11d3 0x0001 0x0000.00ae9302 0x02000013 0x0000.000.00000000 0x00000001 0x00000000 1402532191
/////////////
//session 2:
/////////////
##用scott使用者登陸,注意這裡不能用SYS使用者登入,否則set transaction read only不起作用,記錄下snapshot SCN的近似值
sqlplus scott/sdfg0987
select 'scn before read only: '||to_char(timestamp_to_scn(sysdate),'xxxxxxxx') from dual;
'SCNBEFOREREADONLY:'||TO_CHAR(T
-------------------------------
scn before read only: ae931d
set transaction read only;
##記錄當前會話的統計資訊,system.clean_stat裡存放了與本次測試有關的統計
select st.name,my.value from system.clean_stat st,v$mystat my where my.statistic#=st.statistic#;
NAME VALUE
---------------------------------------------------------------- ----------
commit cleanouts 0
commit cleanouts successfully completed 0
redo KB read for transport 0
file io wait time 27395
gc cr blocks served 0
transaction tables consistent reads - undo records applied 0
transaction tables consistent read rollbacks 0
cleanouts only - consistent read gets 0
cleanouts and rollbacks - consistent read gets 0
immediate (CR) block cleanout applications 0
deferred (CURRENT) block cleanout applications 0
/////////////
//session 1:
/////////////
commit;
alter system flush buffer_cache;
alter system dump datafile 4 block 1779;
--摘錄data block dump結果中Itl有關的部分,事務使用0x02槽位,xid、uba與undo header 0x1e事務槽中的值匹配,這裡很明顯這裡發生了delayed block clean out
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0002.000.000011d2 0x02000010.187e.18 C--- 0 scn 0x0000.00ae9241
0x02 0x0002.01e.000011d2 0x02000010.187e.19 ---- 1 fsc 0x0002.00000000
0x03 0x0002.017.00001139 0x02000011.1788.1f C--- 0 scn 0x0000.00adb1b6
/////////////
//session 3:
/////////////
##db_block_size=8k的情況下,undo segment中有34個slot,執行下面的儲存過程完成340次的update,平均每個slot將被覆蓋10次
##執行後輸出歷次所使用的xid,uba資訊
--第1輪覆蓋
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :1 2,31,4563,8,20,27,6270
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :2 2,2,4565,8,20,28,6270
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :3 2,22,4564,8,20,29,6270
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :4 2,15,4563,8,20,30,6270
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :5 2,3,4567,8,20,31,6270
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :6 2,7,4566,8,20,32,6270
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :7 2,13,4565,8,20,33,6270
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :8 2,0,4565,8,20,34,6270
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :9 2,19,4561,8,20,35,6270
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :10 2,28,4564,8,20,36,6270
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :11 2,29,4565,8,20,37,6270
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :12 2,4,4561,8,20,38,6270
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :13 2,6,4566,8,20,39,6270
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :14 2,25,4566,8,20,40,6270
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :15 2,16,4567,8,20,41,6270
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :16 2,11,4564,8,20,42,6270
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :17 2,23,4565,8,20,43,6270
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :18 2,33,4564,8,20,44,6270
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :19 2,1,4563,8,20,45,6270
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :20 2,26,4565,8,20,46,6270
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :21 2,24,4564,8,20,47,6270
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :22 2,17,4568,8,16,26,6270
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :23 2,8,4567,8,16,27,6270
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :24 2,20,4565,8,16,28,6270
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :25 2,21,4565,8,16,29,6270
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :26 2,32,4566,8,16,30,6270
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :27 2,12,4562,8,16,31,6270
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :28 2,27,4567,8,16,32,6270
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :29 2,5,4564,8,16,33,6270
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :30 2,14,4565,8,16,34,6270
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :31 2,30,4563,8,16,35,6270
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :32 2,9,4566,8,16,36,6270
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :33 2,10,4567,8,21,1,6270
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :34 2,18,4564,8,21,2,6270
。。。。省略了部分
--第10輪覆蓋
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :307 2,31,4572,8,14,3,6271
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :308 2,2,4574,8,14,4,6271
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :309 2,22,4573,8,14,5,6271
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :310 2,15,4572,8,14,6,6271
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :311 2,3,4576,8,14,7,6271
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :312 2,7,4575,8,14,8,6271
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :313 2,13,4574,8,14,9,6271
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :314 2,0,4574,8,14,10,6271
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :315 2,19,4570,8,14,11,6271
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :316 2,28,4573,8,14,12,6271
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :317 2,29,4574,8,14,13,6271
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :318 2,4,4570,8,14,14,6271
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :319 2,6,4575,8,14,15,6271
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :320 2,25,4575,8,14,16,6271
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :321 2,16,4576,8,14,17,6271
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :322 2,11,4573,8,14,18,6271
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :323 2,23,4574,8,14,19,6271
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :324 2,33,4573,8,14,20,6271
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :325 2,1,4572,8,14,21,6271
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :326 2,26,4574,8,14,22,6271
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :327 2,24,4573,8,14,23,6271
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :328 2,17,4577,8,14,24,6271
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :329 2,8,4576,8,14,25,6271
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :330 2,20,4574,8,14,26,6271
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :331 2,21,4574,8,14,27,6271
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :332 2,32,4575,8,14,28,6271
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :333 2,12,4571,8,14,29,6271
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :334 2,27,4576,8,14,30,6271
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :335 2,5,4573,8,14,31,6271
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :336 2,14,4574,8,14,32,6271
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :337 2,30,4572,8,14,33,6271
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :338 2,9,4575,8,14,34,6271
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :339 2,10,4576,8,15,1,6271
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :340 2,18,4573,8,15,2,6271
alter system flush buffer_cache;
alter system dump undo header "_SYSSMU2$";
--undo header dump結果,發現slot:0x1e 的wrap#從0x11d2->0x11dc,的確覆蓋了10次,control scn:00ae96dc遠大於 Session 2查詢開始時的scn:ae931d,也預示著Session 2的查詢將觸發undo事務表回滾
TRN CTL:: seq: 0x187f chd: 0x001f ctl: 0x0012 inc: 0x00000000 nfb: 0x0001
mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x0200000f.187f.02 scn: 0x0000.00ae96dc
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x00 9 0x00 0x11de 0x0013 0x0000.00ae96f4 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364
0x01 9 0x00 0x11dc 0x001a 0x0000.00ae9715 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364
0x02 9 0x00 0x11de 0x0016 0x0000.00ae96e2 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364
0x03 9 0x00 0x11e0 0x0007 0x0000.00ae96eb 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364
0x04 9 0x00 0x11da 0x0006 0x0000.00ae9700 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364
0x05 9 0x00 0x11dd 0x000e 0x0000.00ae9733 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364
0x06 9 0x00 0x11df 0x0019 0x0000.00ae9703 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364
0x07 9 0x00 0x11df 0x000d 0x0000.00ae96ee 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364
0x08 9 0x00 0x11e0 0x0014 0x0000.00ae9721 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364
0x09 9 0x00 0x11df 0x000a 0x0000.00ae973c 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364
0x0a 9 0x00 0x11e0 0x0012 0x0000.00ae973f 0x0200000f 0x0000.000.00000000 0x00000001 0x00000000 1402532364
0x0b 9 0x00 0x11dd 0x0017 0x0000.00ae970c 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364
0x0c 9 0x00 0x11db 0x001b 0x0000.00ae972d 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364
0x0d 9 0x00 0x11de 0x0000 0x0000.00ae96f1 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364
0x0e 9 0x00 0x11de 0x001e 0x0000.00ae9736 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364
0x0f 9 0x00 0x11dc 0x0003 0x0000.00ae96e8 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364
0x10 9 0x00 0x11e0 0x000b 0x0000.00ae9709 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364
0x11 9 0x00 0x11e1 0x0008 0x0000.00ae971e 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364
0x12 9 0x00 0x11dd 0xffff 0x0000.00ae9742 0x0200000f 0x0000.000.00000000 0x00000001 0x00000000 1402532364
0x13 9 0x00 0x11da 0x001c 0x0000.00ae96f7 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364
0x14 9 0x00 0x11de 0x0015 0x0000.00ae9724 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364
0x15 9 0x00 0x11de 0x0020 0x0000.00ae9727 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364
0x16 9 0x00 0x11dd 0x000f 0x0000.00ae96e5 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364
0x17 9 0x00 0x11de 0x0021 0x0000.00ae970f 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364
0x18 9 0x00 0x11dd 0x0011 0x0000.00ae971b 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364
0x19 9 0x00 0x11df 0x0010 0x0000.00ae9706 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364
0x1a 9 0x00 0x11de 0x0018 0x0000.00ae9718 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364
0x1b 9 0x00 0x11e0 0x0005 0x0000.00ae9730 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364
0x1c 9 0x00 0x11dd 0x001d 0x0000.00ae96fa 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364
0x1d 9 0x00 0x11de 0x0004 0x0000.00ae96fd 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364
0x1e 9 0x00 0x11dc 0x0009 0x0000.00ae9739 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364
0x1f 9 0x00 0x11dc 0x0002 0x0000.00ae96df 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364
0x20 9 0x00 0x11df 0x000c 0x0000.00ae972a 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364
0x21 9 0x00 0x11dd 0x0001 0x0000.00ae9712 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364
##dump 所有的undo block,以便後面作分析
alter system dump datafile 8 block min 9 block max 31;
/////////////
//session 2:
/////////////
##遍歷scott.tabnow1表,查到了session 1修改前的複製
select * from scott.tabnow1;
USERNAME USER_ID CREATED
------------------------------ ---------- ------------
XS$NULLLL 2147483638 21-OCT-11
NEWUSER 84 12-MAR-14
SCOTTTTT 83 21-OCT-11
OWBSYS_AUDIT 82 21-OCT-11
OWBSYS 78 21-OCT-11
APEX 77 21-OCT-11
APEX_PUBLIC 75 21-OCT-11
FLOWS_FILE 74 21-OCT-11
MGMT_VIEW 73 21-OCT-11
DDD 34 28-MAY-14
##會話中事務表一致性讀相關的統計值增加
select st.name,my.value from system.clean_stat st,v$mystat my where my.statistic#=st.statistic#;
NAME VALUE
---------------------------------------------------------------- ----------
commit cleanouts 0
commit cleanouts successfully completed 0
redo KB read for transport 0
file io wait time 40974
gc cr blocks served 0
transaction tables consistent reads - undo records applied 310
transaction tables consistent read rollbacks 1
cleanouts only - consistent read gets 0
cleanouts and rollbacks - consistent read gets 1
immediate (CR) block cleanout applications 1
deferred (CURRENT) block cleanout applications 0
針對上述測試結果的分析:
Undo header 事務表中的每個slot被覆蓋多達10次,session 2還能正常返回scott.tabnow1表修改前的記錄,就是利用了undo事務表的回滾機制,為了實現這個回滾:當新的事務開始使用某個undo事務表槽之前會將事務表TRN CTL部分的scn與uba兩個值作為ctl max scn、uba欄位儲存到新事務所擁有的undo block裡,也會將這個事務表槽中的scn與dba兩個值作為prv tx scn、prev brb欄位儲存到新事務所擁有的undo block裡,例如session 1 中事務使用的uba為8/16 Rec #0x19:
*-----------------------------
* Rec #0x19 slt: 0x1e objn: 90344(0x000160e8) objd: 90344 tblspc: 8(0x00000008)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000Ext idx: 0
flg2: 0
*-----------------------------
uba: 0x02000010.187e.18 ctl max scn: 0x0000.00ae9095 prv tx scn: 0x0000.00ae909b
txn start scn: scn: 0x0000.00ae90b2 logon user: 0
prev brb: 33554460 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: L itl: xid: 0x0002.004.0000111f uba: 0x0200001a.177b.09
flg: C--- lkc: 0 scn: 0x0000.00ad9579
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x010006f3 hdba: 0x010006f2
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
ncol: 3 nnew: 1 size: 2
col 0: [ 9] 58 53 24 4e 55 4c 4c 4c 4c
上述dump中uba: 0x02000010.187e.18 ctl max scn: 0x0000.00ae9095正是來自於事務開始前undo header裡TRN CTL部分所包含的uba: 0x02000010.187e.18 scn: 0x0000.00ae9095,而prv tx scn:0x0000.00ae909b 和prev brb:33554460 則分別等於事務開始前slot 0x1e 所對應的scn:00ae909b和dba:0x0200001c(轉換成10進位制就是33554460)。只有事務的首條undo記錄才會保留上一版本的undo資訊。將相關的undo記錄串起來就可以完整的回放事務表所發生過的變更,下面較為直觀的方式說明一下undo 記錄間的指向關係,還是以proc1.sql裡前三個事務為例,它們所使用的xid,uba如下:
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN : 2,31,4563,8,20,27,6270
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN : 2,2,4565,8,20,28,6270
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN : 2,22,4564,8,20,29,6270
Undo record的記錄是後修改的指向先修改的,所以依次dump block 8/20中 Rec #0x1d、Rec #0x1c、 Rec #0x1b的記錄,僅將相關的內容摘錄下來:
*-----------------------------
* Rec #0x1d slt: 0x16 objn: 89731(0x00015e83) objd: 89731 tblspc: 8(0x00000008)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000Ext idx: 0
flg2: 0
*-----------------------------
uba: 0x02000014.187e.1c ctl max scn: 0x0000.00ae92ed prv tx scn: 0x0000.00ae92ee
txn start scn: scn: 0x0000.00ae9357 logon user: 0
prev brb: 33554451 prev bcl: 0
*-----------------------------
* Rec #0x1c slt: 0x02 objn: 89731(0x00015e83) objd: 89731 tblspc: 8(0x00000008)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000Ext idx: 0
flg2: 0
*-----------------------------
uba: 0x02000014.187e.1b ctl max scn: 0x0000.00ae92ec prv tx scn: 0x0000.00ae92ed
txn start scn: scn: 0x0000.00ae9354 logon user: 0
prev brb: 33554451 prev bcl: 0
*-----------------------------
* Rec #0x1b slt: 0x1f objn: 89731(0x00015e83) objd: 89731 tblspc: 8(0x00000008)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000Ext idx: 0
flg2: 0
*-----------------------------
uba: 0x02000014.187e.18 ctl max scn: 0x0000.00ae92eb prv tx scn: 0x0000.00ae92ec
txn start scn: scn: 0x0000.00000000 logon user: 0
prev brb: 33554451 prev bcl: 0
透過這三個undo Record能夠對映得到前一時刻的undo事務表,假設T3>T2>T1>T0,以下圖示能夠直觀的展現這一對映關係:
上面的圖能夠幫助我們理解oracle是如何實現事務表的回滾,只要undo block不被覆蓋,就能回滾得到足夠舊的事務表。
具備了上述知識點後,我們分析一下session 2是如何成功遍歷scott.tabnow1表的:
(1) Session 2的select語句執行時的snapshot scn近似等於ae931d
(2) 訪問rdba:4/1779 時發現Itl列表裡slot 0x02還處於活動狀態,且沒有commit scn,於是就到xid所指向的undo 事務表進一步查詢事務是否提交,slot 0x1e的state=9表示事務已經提交,但提交時的wrap#值為0x11dc遠大於0x11d2,且undo 事務表頭的control scn:ae96dc遠大於snapshot scn: ae931d
(3) Oracle判斷事務表槽已slot 0x1e已經被覆蓋,需要回滾事務表,回滾的起點是8/14 Rec #0x21,這個Record是proc1.sql中第337個transaction所使用的undo record,因為正是這個transaction完成了對undo slot 0x1e的最後一次覆蓋
(4) 接下來將如上圖所示,oracle一步一步的對事務表進行回滾,每重構出一個事務表的版本後,將事務表頭部的control scn和snapshot scn進行比較,如果snapshot scn
select utl_raw.cast_to_varchar2(replace('58 53 24 4e 55 4c 4c 4c 4c',' ')) from dual;
UTL_RAW.CAST_TO_VARCHAR2(REPLACE('5853244E554C4C4C4C',''))
--------------------------------------------------------------------------------
XS$NULLLL
Session 2實現一致性讀的兩個統計資訊:
NAME VALUE
---------------------------------------------------------------- ----------
transaction tables consistent reads - undo records applied 310
transaction tables consistent read rollbacks 1
transaction tables consistent reads - undo records applied 310
表示為了回滾事務表應用了多少undo records,這裡為何是310,暫無法證實,我本來認為應該是338的
transaction tables consistent read rollbacks 1
表示事務表發生了一次回滾的動作
之前我們曾經在session 3裡透過alter system dump datafile 8 block min 9 block max 31將所有的undo block dump出來,現在回過頭來看一下dump的結果在undo Record 8/17 Rec #0x1裡找到了uba: 0x02000010.187e.19,即存放修改前複製的undo record:8/16 Rec #0x19,說明這個undo record還在undo chain裡
*-----------------------------
* Rec #0x1 slt: 0x13 objn: 270(0x0000010e) objd: 268 tblspc: 1(0x00000001)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000Ext idx: 0
flg2: 0
*-----------------------------
uba: 0x02000010.187e.19 ctl max scn: 0x0000.00ae909b prv tx scn: 0x0000.00ae909d
txn start scn: scn: 0x0000.00ae9294 logon user: 0
prev brb: 33554461 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: L itl: xid: 0x0002.006.000011d2 uba: 0x0200001f.1877.01
flg: C--- lkc: 0 scn: 0x0000.00ae90b2
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x00810de8 hdba: 0x00800192
itli: 2 ispac: 0 maxfr: 4858
tabn: 1 slot: 5(0x5)
##進一步dump 8/16 Rec #0x19的內容,可以看到修改前的內容還在,所以session 2能完成表的遍歷
*-----------------------------
* Rec #0x19 slt: 0x1e objn: 90344(0x000160e8) objd: 90344 tblspc: 8(0x00000008)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000Ext idx: 0
flg2: 0
*-----------------------------
uba: 0x02000010.187e.18 ctl max scn: 0x0000.00ae9095 prv tx scn: 0x0000.00ae909b
txn start scn: scn: 0x0000.00ae90b2 logon user: 0
prev brb: 33554460 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: L itl: xid: 0x0002.004.0000111f uba: 0x0200001a.177b.09
flg: C--- lkc: 0 scn: 0x0000.00ad9579
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x010006f3 hdba: 0x010006f2
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
ncol: 3 nnew: 1 size: 2
col 0: [ 9] 58 53 24 4e 55 4c 4c 4c 4c
/////////////
//session 4:
/////////////
##對proc1.sql指令碼的迴圈次數作略微調整後,分別在迴圈34次、迴圈68次、迴圈102次的情況下,繼續對事務槽作覆蓋,平均每個事務槽再被覆蓋6次,執行調整後的proc1.sql,輸出如下:
--proc1.sql調整為迴圈34次時的輸出:
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :1 2,2,4575,8,15,17,6271
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :2 2,22,4574,8,15,18,6271
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :3 2,15,4573,8,15,19,6271
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :4 2,3,4577,8,15,20,6271
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :5 2,7,4576,8,15,21,6271
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :6 2,13,4575,8,15,22,6271
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :7 2,0,4575,8,15,23,6271
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :8 2,19,4571,8,15,24,6271
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :9 2,28,4574,8,15,25,6271
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :10 2,29,4575,8,15,26,6271
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :11 2,4,4571,8,15,27,6271
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :12 2,6,4576,8,24,1,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :13 2,25,4576,8,24,2,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :14 2,16,4577,8,24,3,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :15 2,11,4574,8,24,4,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :16 2,23,4575,8,24,5,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :17 2,33,4574,8,24,6,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :18 2,1,4573,8,24,7,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :19 2,26,4575,8,24,8,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :20 2,24,4574,8,24,9,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :21 2,17,4578,8,24,10,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :22 2,8,4577,8,24,11,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :23 2,20,4575,8,24,12,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :24 2,21,4575,8,24,13,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :25 2,32,4576,8,24,14,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :26 2,12,4572,8,24,15,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :27 2,27,4577,8,24,16,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :28 2,5,4574,8,24,17,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :29 2,14,4575,8,24,18,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :30 2,30,4573,8,24,19,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :31 2,9,4576,8,24,20,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :32 2,10,4577,8,24,21,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :33 2,18,4574,8,24,22,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :34 2,31,4574,8,24,23,6272
--proc1.sql調整為迴圈68次時的輸出:
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :1 2,21,4576,8,26,1,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :2 2,32,4577,8,26,2,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :3 2,12,4573,8,26,3,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :4 2,27,4578,8,26,4,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :5 2,5,4575,8,26,5,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :6 2,14,4576,8,26,6,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :7 2,30,4574,8,26,7,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :8 2,9,4577,8,26,8,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :9 2,10,4578,8,26,9,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :10 2,18,4575,8,26,10,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :11 2,31,4575,8,26,11,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :12 2,2,4577,8,26,12,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :13 2,22,4576,8,26,13,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :14 2,15,4575,8,26,14,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :15 2,3,4579,8,26,15,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :16 2,7,4578,8,26,16,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :17 2,13,4577,8,26,17,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :18 2,0,4577,8,26,18,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :19 2,19,4573,8,26,19,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :20 2,28,4576,8,26,20,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :21 2,29,4577,8,26,21,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :22 2,4,4573,8,26,22,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :23 2,6,4578,8,26,23,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :24 2,25,4578,8,26,24,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :25 2,16,4579,8,26,25,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :26 2,11,4576,8,26,26,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :27 2,23,4577,8,26,27,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :28 2,33,4576,8,26,28,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :29 2,1,4575,8,26,29,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :30 2,26,4577,8,26,30,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :31 2,24,4576,8,26,31,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :32 2,17,4580,8,26,32,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :33 2,8,4579,8,26,33,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :34 2,20,4577,8,26,34,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :35 2,21,4577,8,27,1,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :36 2,32,4578,8,27,2,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :37 2,12,4574,8,27,3,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :38 2,27,4579,8,27,4,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :39 2,5,4576,8,27,5,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :40 2,14,4577,8,27,6,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :41 2,30,4575,8,27,7,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :42 2,9,4578,8,27,8,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :43 2,10,4579,8,27,9,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :44 2,18,4576,8,27,10,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :45 2,31,4576,8,27,11,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :46 2,2,4578,8,27,12,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :47 2,22,4577,8,27,13,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :48 2,15,4576,8,27,14,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :49 2,3,4580,8,27,15,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :50 2,7,4579,8,27,16,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :51 2,13,4578,8,27,17,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :52 2,0,4578,8,27,18,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :53 2,19,4574,8,27,19,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :54 2,28,4577,8,27,20,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :55 2,29,4578,8,27,21,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :56 2,4,4574,8,27,22,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :57 2,6,4579,8,27,23,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :58 2,25,4579,8,27,24,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :59 2,16,4580,8,27,25,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :60 2,11,4577,8,27,26,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :61 2,23,4578,8,27,27,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :62 2,33,4577,8,27,28,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :63 2,1,4576,8,27,29,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :64 2,26,4578,8,27,30,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :65 2,24,4577,8,27,31,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :66 2,17,4581,8,27,32,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :67 2,8,4580,8,27,33,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :68 2,20,4578,8,27,34,6272
--proc1.sql調整為迴圈102次時的輸出:
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :1 2,21,4578,8,28,1,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :2 2,32,4579,8,28,2,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :3 2,12,4575,8,28,3,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :4 2,27,4580,8,28,4,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :5 2,5,4577,8,28,5,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :6 2,14,4578,8,28,6,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :7 2,30,4576,8,28,7,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :8 2,9,4579,8,28,8,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :9 2,10,4580,8,28,9,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :10 2,18,4577,8,28,10,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :11 2,31,4577,8,28,11,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :12 2,2,4579,8,28,12,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :13 2,22,4578,8,28,13,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :14 2,15,4577,8,28,14,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :15 2,3,4581,8,28,15,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :16 2,7,4580,8,28,16,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :17 2,13,4579,8,28,17,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :18 2,0,4579,8,28,18,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :19 2,19,4575,8,28,19,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :20 2,28,4578,8,28,20,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :21 2,29,4579,8,28,21,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :22 2,4,4575,8,28,22,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :23 2,6,4580,8,28,23,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :24 2,25,4580,8,28,24,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :25 2,16,4581,8,28,25,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :26 2,11,4578,8,28,26,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :27 2,23,4579,8,28,27,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :28 2,33,4578,8,28,28,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :29 2,1,4577,8,28,29,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :30 2,26,4579,8,28,30,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :31 2,24,4578,8,28,31,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :32 2,17,4582,8,28,32,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :33 2,8,4581,8,28,33,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :34 2,20,4579,8,28,34,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :35 2,21,4579,8,29,1,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :36 2,32,4580,8,29,2,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :37 2,12,4576,8,29,3,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :38 2,27,4581,8,29,4,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :39 2,5,4578,8,29,5,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :40 2,14,4579,8,29,6,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :41 2,30,4577,8,29,7,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :42 2,9,4580,8,29,8,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :43 2,10,4581,8,29,9,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :44 2,18,4578,8,29,10,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :45 2,31,4578,8,29,11,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :46 2,2,4580,8,29,12,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :47 2,22,4579,8,29,13,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :48 2,15,4578,8,29,14,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :49 2,3,4582,8,29,15,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :50 2,7,4581,8,29,16,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :51 2,13,4580,8,29,17,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :52 2,0,4580,8,29,18,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :53 2,19,4576,8,29,19,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :54 2,28,4579,8,29,20,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :55 2,29,4580,8,29,21,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :56 2,4,4576,8,29,22,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :57 2,6,4581,8,29,23,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :58 2,25,4581,8,29,24,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :59 2,16,4582,8,29,25,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :60 2,11,4579,8,29,26,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :61 2,23,4580,8,29,27,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :62 2,33,4579,8,29,28,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :63 2,1,4578,8,29,29,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :64 2,26,4580,8,29,30,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :65 2,24,4579,8,29,31,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :66 2,17,4583,8,29,32,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :67 2,8,4582,8,29,33,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :68 2,20,4580,8,29,34,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :69 2,21,4580,8,30,1,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :70 2,32,4581,8,30,2,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :71 2,12,4577,8,30,3,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :72 2,27,4582,8,30,4,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :73 2,5,4579,8,30,5,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :74 2,14,4580,8,30,6,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :75 2,30,4578,8,30,7,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :76 2,9,4581,8,30,8,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :77 2,10,4582,8,30,9,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :78 2,18,4579,8,30,10,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :79 2,31,4579,8,30,11,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :80 2,2,4581,8,30,12,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :81 2,22,4580,8,30,13,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :82 2,15,4579,8,30,14,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :83 2,3,4583,8,30,15,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :84 2,7,4582,8,30,16,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :85 2,13,4581,8,30,17,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :86 2,0,4581,8,30,18,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :87 2,19,4577,8,30,19,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :88 2,28,4580,8,30,20,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :89 2,29,4581,8,30,21,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :90 2,4,4577,8,30,22,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :91 2,6,4582,8,30,23,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :92 2,25,4582,8,30,24,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :93 2,16,4583,8,30,25,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :94 2,11,4580,8,30,26,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :95 2,23,4581,8,30,27,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :96 2,33,4580,8,30,28,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :97 2,1,4579,8,30,29,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :98 2,26,4581,8,30,30,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :99 2,24,4580,8,30,31,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :100 2,17,4584,8,30,32,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :101 2,8,4583,8,30,34,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :102 2,20,4581,8,30,35,6272
/////////////
//session 2:
/////////////
##在session 4對每個事務槽各覆蓋6遍的基礎上,再次遍歷scott.tabnow1表,依然能查到scott.tabnow1修改前的複製
select * from scott.tabnow1;
USERNAME USER_ID CREATED
------------------------------ ---------- ------------
XS$NULLLL 2147483638 21-OCT-11
NEWUSER 84 12-MAR-14
SCOTTTTT 83 21-OCT-11
OWBSYS_AUDIT 82 21-OCT-11
OWBSYS 78 21-OCT-11
APEX 77 21-OCT-11
APEX_PUBLIC 75 21-OCT-11
FLOWS_FILE 74 21-OCT-11
MGMT_VIEW 73 21-OCT-11
DDD 34 28-MAY-14
在session 4再次嘗試覆蓋的過程中,透過proc1.sql執行輸出的xid,uba資訊獲知undo record :8/25 Rec#0x19並沒有被使用到.
##dump 所有undo塊
alter system dump datafile 8 block min 9 block max 31;
##在生成的dump檔案中,尋找到8/25 Rec#0x19對應的uba地址:02000010.187e.19仍然包含在8/17 Rec#0x1中,且8/25 Rec#0x19裡仍存有修改前的資料複製,這樣既確保事務表正常回滾,也確保修改前的複製能被正常讀取
--8/17 Rec #0x1包含有到uba: 0x02000010.187e.19的指向
*-----------------------------
* Rec #0x1 slt: 0x13 objn: 270(0x0000010e) objd: 268 tblspc: 1(0x00000001)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000Ext idx: 0
flg2: 0
*-----------------------------
uba: 0x02000010.187e.19 ctl max scn: 0x0000.00ae909b prv tx scn: 0x0000.00ae909d
txn start scn: scn: 0x0000.00ae9294 logon user: 0
prev brb: 33554461 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: L itl: xid: 0x0002.006.000011d2 uba: 0x0200001f.1877.01
flg: C--- lkc: 0 scn: 0x0000.00ae90b2
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x00810de8 hdba: 0x00800192
itli: 2 ispac: 0 maxfr: 4858
tabn: 1 slot: 5(0x5)
--8/25 Rec#0x19包含有資料修改前的複製
*-----------------------------
* Rec #0x19 slt: 0x1e objn: 90344(0x000160e8) objd: 90344 tblspc: 8(0x00000008)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000Ext idx: 0
flg2: 0
*-----------------------------
uba: 0x02000010.187e.18 ctl max scn: 0x0000.00ae9095 prv tx scn: 0x0000.00ae909b
txn start scn: scn: 0x0000.00ae90b2 logon user: 0
prev brb: 33554460 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: L itl: xid: 0x0002.004.0000111f uba: 0x0200001a.177b.09
flg: C--- lkc: 0 scn: 0x0000.00ad9579
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x010006f3 hdba: 0x010006f2
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
ncol: 3 nnew: 1 size: 2
col 0: [ 9] 58 53 24 4e 55 4c 4c 4c 4c
select utl_raw.cast_to_varchar2(replace('58 53 24 4e 55 4c 4c 4c 4c',' ')) from dual;
UTL_RAW.CAST_TO_VARCHAR2(REPLACE('5853244E554C4C4C4C',''))
--------------------------------------------------------------------------------
XS$NULLLL
/////////////
//session 4:
/////////////
##下面再次呼叫proc1.sql,迴圈340次,這才形成了致命一擊,proc1.sql輸出如下:
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :1 2,21,4581,8,31,2,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :2 2,32,4582,8,31,3,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :3 2,12,4578,8,31,4,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :4 2,27,4583,8,31,5,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :5 2,5,4580,8,31,6,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :6 2,14,4581,8,31,7,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :7 2,30,4579,8,31,8,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :8 2,9,4582,8,31,9,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :9 2,10,4583,8,31,10,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :10 2,18,4580,8,31,11,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :11 2,31,4580,8,31,12,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :12 2,2,4582,8,31,13,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :13 2,22,4581,8,31,14,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :14 2,15,4580,8,31,15,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :15 2,3,4584,8,31,16,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :16 2,7,4583,8,31,17,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :17 2,13,4582,8,31,18,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :18 2,0,4582,8,31,19,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :19 2,19,4578,8,31,20,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :20 2,28,4581,8,31,21,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :21 2,29,4582,8,31,22,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :22 2,4,4578,8,31,23,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :23 2,6,4583,8,31,24,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :24 2,25,4583,8,31,25,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :25 2,16,4584,8,31,26,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :26 2,11,4581,8,31,27,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :27 2,23,4582,8,31,28,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :28 2,33,4581,8,31,29,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :29 2,1,4580,8,31,30,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :30 2,26,4582,8,31,31,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :31 2,24,4581,8,31,32,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :32 2,17,4585,8,31,33,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :33 2,8,4584,8,31,34,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :34 2,20,4582,8,31,35,6272
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :35 2,21,4582,8,16,1,6273
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :36 2,32,4583,8,16,2,6273
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :37 2,12,4579,8,16,3,6273
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :38 2,27,4584,8,16,4,6273
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :39 2,5,4581,8,16,5,6273
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :40 2,14,4582,8,16,6,6273
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :41 2,30,4580,8,16,7,6273
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :42 2,9,4583,8,16,8,6273
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :43 2,10,4584,8,16,9,6273
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :44 2,18,4581,8,16,10,6273
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :45 2,31,4581,8,16,11,6273
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :46 2,2,4583,8,16,12,6273
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :47 2,22,4582,8,16,13,6273
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :48 2,15,4581,8,16,14,6273
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :49 2,3,4585,8,16,15,6273
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :50 2,7,4584,8,16,16,6273
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :51 2,13,4583,8,16,17,6273
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :52 2,0,4583,8,16,18,6273
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :53 2,19,4579,8,16,19,6273
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :54 2,28,4582,8,16,20,6273
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :55 2,29,4583,8,16,21,6273
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :56 2,4,4579,8,16,22,6273
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :57 2,6,4584,8,16,23,6273
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :58 2,25,4584,8,16,24,6273
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :59 2,16,4585,8,16,25,6273
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :60 2,11,4582,8,16,26,6273
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :61 2,23,4583,8,16,27,6273
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :62 2,33,4582,8,16,28,6273
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :63 2,1,4581,8,16,29,6273
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :64 2,26,4583,8,16,30,6273
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :65 2,24,4582,8,16,31,6273
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :66 2,17,4586,8,16,32,6273
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :67 2,8,4585,8,16,33,6273
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :68 2,20,4583,8,16,34,6273
XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :69 2,21,4583,8,17,1,6273
。。。。。。省略了後面的271行
##從上面的輸出可以看到8/16 Rec #0x19,8/17 Rec#0x1先後被覆蓋,前者包含資料修改前的複製,後者包含了事務槽回滾所需的uba資訊,這時必然導致下面session 2遇到ORA-01555錯誤
/////////////
//session 2:
/////////////
##久違的ORA-01555錯誤終於來了
select * from scott.tabnow1
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 2 with name "_SYSSMU2$"
too small
alter system dump datafile 8 block min 9 block max 31;
##把所有undo block dump到檔案stest2_ora_29753578.trc
alter system dump datafile 8 block min 9 block max 31;
##在trace檔案中未能查到02000010.187e.19,意味著事務表不能回溯到足夠舊的版本
cat stest2_ora_29753578.trc | grep 02000010.187e.19 | wc –l
0
##在8/16 #0x19中的記錄也已經被覆蓋掉了,意味著即使事務表即使能夠順利回滾,但也找不到修改前的資料複製了
*-----------------------------
* Rec #0x19 slt: 0x10 objn: 89731(0x00015e83) objd: 89731 tblspc: 8(0x00000008)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000Ext idx: 0
flg2: 0
*-----------------------------
uba: 0x02000010.1881.18 ctl max scn: 0x0000.00ae9c40 prv tx scn: 0x0000.00ae9c43
txn start scn: scn: 0x0000.00ae9ca6 logon user: 0
prev brb: 33554463 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: L itl: xid: 0x0002.006.000011e8 uba: 0x02000010.1881.17
flg: C--- lkc: 0 scn: 0x0000.00ae9ca3
Array Update of 2 rows:
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 1
ncol: 1 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x010005be hdba: 0x010005ba
itli: 2 ispac: 0 maxfr: 4858
vect = 0
col 0: [ 2] c1 3b
tabn: 0 slot: 1(0x1) flag: 0x2c lock: 0 ckix: 1
ncol: 1 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x010005be hdba: 0x010005ba
itli: 2 ispac: 0 maxfr: 4858
vect = 0
col 0: [ 2] c1 3b
附proc1.sql:
///Proc1.sql///
set serveroutput on
declare
v_xidusn number;
v_xidslot number;
v_xidsqn number;
v_ubafil number;
v_ubablk number;
v_ubasqn number;
v_ubarec number;
v_cnt number:=0;
v_rownum number;
v_startscn varchar2(20);
v_rowid rowid;
begin
select to_char(timestamp_to_scn(sysdate),'xxxxxxxx') into v_startscn from dual;
dbms_output.put_line('START SCN:'||v_startscn);
for i in 1..340 loop
update scott.t1 set id = i;
select XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN into v_xidusn,v_xidslot,v_xidsqn,v_ubafil,v_ubablk,v_ubarec,v_ubasqn from v$transaction;
v_cnt:=v_cnt+1;
dbms_output.put_line('XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :'||v_cnt||' '||v_xidusn||','||v_xidslot||','||v_xidsqn||','||v_ubafil||','||v_ubablk||','||v_ubarec||','||v_ubasqn);
commit;
end loop;
end;
/
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/8494287/viewspace-1411699/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 17_深入解析Oracle undo原理(1)_transactionOracle
- 請你說說 Vue 中 slot 和 slot-scope 的原理(2.6.11 深度解析)Vue
- 如何找回oracle中被覆蓋的包體程式Oracle
- 恢復被覆蓋的儲存過程 oracle儲存過程Oracle
- [z] 利用閃回恢復被覆蓋的Oracle PACKAGE包OraclePackage
- 避免resolv.conf設定被覆蓋
- undo的工作原理
- 重灌Windows後LILO被覆蓋的解決辦法(轉)Windows
- itl在被覆蓋之前被儲存了下來!
- itl在被覆蓋之前被儲存了下來1!
- 用k*k的方格覆蓋單元格(i,j),單元格可以被覆蓋幾次?
- ora-01555模擬以及undo tbs的guarantee特性測試!
- Transaction註解原理
- 19_深入解析Oracle undo原理(3)_ktuxe詳解OracleUX
- 20_深入解析Oracle undo原理(4)_ktuxc詳解OracleUX
- CTEX安裝必須注意 系統變數 path 被覆蓋變數
- InnoDB undo log原理
- 重名就會被覆蓋?那JavaScript中是如何實現過載的呢?JavaScript
- 檔案替換後怎麼恢復,恢復被覆蓋的檔案
- undo機制工作原理描述的:
- 解決 flutter module 中 .android 和 .ios 目錄不被覆蓋的問題FlutterAndroidiOS
- 18_深入解析Oracle undo原理(2)_undo表空間使用率100%問題處理Oracle
- oracle undo segment header 事務表transaction table系列一OracleHeader
- Redo 和 Undo 概念解析
- undo壞塊引起資料庫無法啟動資料庫
- 事務註解(@Transactional)引起的資料覆蓋故障
- [Vue] slot詳解,slot、slot-scope和v-slotVue
- Control File中備份資訊被覆蓋情況下的資料庫恢復案例資料庫
- 專題之Undo工作原理剖析
- Laravel 中兩張資料表 left join 怎麼讓相同欄位不被覆蓋?Laravel
- [原始碼解析] Flink的Slot究竟是什麼?(1)原始碼
- [原始碼解析] Flink的Slot究竟是什麼?(2)原始碼
- MySQL redo與undo日誌解析MySql
- SAP 物料主分類檢視維護的批次分類特性值不能在批次裡被覆蓋
- Control File中備份資訊被覆蓋情況下的資料庫恢復案例 (zt)資料庫
- FLASHBACK_TRANSACTION_QUERY 11G R2. UNDO_SQL為NULL的問題SQLNull
- InnoDB purge原理--哪些undo log可purge
- 深入理解vue中的slot與slot-scopeVue