留下了很多疑问，暂时记录一下
slot被覆盖之前会被保存下来吗？这个我想肯定不会.........如果是这样，为什么在覆盖之后没有出现ora-01555?
[@more@]rollback segment rbs2中一共有21个slot，连续启动20个
事务占去20个slot，目的只是为了测试的需要，只留一个slot
不停的操作事务，目的是能让其他事务覆盖slot从而测试consistent
read的过程和ora-01555:
20个事务启动之后的slot使用如下：
session 2:
SQL> select xidslot,start_time from v$transaction order by xidslot;
XIDSLOT START_TIME
------- --------------------
0 04/07/09 09:15:00
1 04/07/09 09:52:09
2 04/07/09 09:15:21
3 04/07/09 09:15:12
4 04/07/09 09:15:38
5 04/07/09 09:14:39
6 04/07/09 09:52:09
7 04/07/09 09:15:00
8 04/07/09 09:15:35
9 04/07/09 09:14:51
10 04/07/09 09:14:57
XIDSLOT START_TIME
------- --------------------
11 04/07/09 09:14:48
13 04/07/09 09:15:09
14 04/07/09 09:15:27
15 04/07/09 09:14:45
16 04/07/09 09:14:54
17 04/07/09 09:15:30
18 04/07/09 09:15:06
19 04/07/09 09:15:12
20 04/07/09 09:14:42
已选择20行。
--==================================
SQL> alter system dump undo header rbs2;
系统已更改。
--=================================
Undo Segment: RBS2 (2)
********************************************************************************
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 5 #blocks: 2559
last map 0x00000000 #maps: 0 offset: 1056
Highwater:: 0x0080084d ext#: 4 blk#: 44 ext size: 512
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 4
Unlocked
Map Header:: next 0x00000000 #extents: 5 obj#: 0 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x00800022 length: 511
0x00800221 length: 512
0x00800421 length: 512
0x00800621 length: 512
0x00800821 length: 512
TRN CTL:: seq: 0x003a chd: 0x000c ctl: 0x000c inc: 0x00000000 nfb: 0x0000
mgc: 0x8002 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x0080084d.003a.0b scn: 0x0000.019ea129
Version: 0x01
FREE BLOCK POOL::
uba: 0x00000000.003a.0a ext: 0x4 spc: 0x24a
uba: 0x00000000.0039.04 ext: 0x3 spc: 0x556
uba: 0x00000000.0034.04 ext: 0x0 spc: 0x5fe
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub stmt_num
------------------------------------------------------------------------------------------------
0x00 10 0x80 0x00cd 0x0000 0x0000.019e9938 0x00800176 0x0000.000.00000000 0x00000001 0x00000000
0x01 10 0x80 0x0141 0x0004 0x0000.019ea106 0x0080084a 0x0000.000.00000000 0x00000001 0x00000000
0x02 10 0x80 0x00c0 0x0000 0x0000.019e993f 0x0080017b 0x0000.000.00000000 0x00000001 0x00000000
0x03 10 0x80 0x00a7 0x0000 0x0000.019e993c 0x0080017a 0x0000.000.00000000 0x00000001 0x00000000
0x04 10 0x80 0x00d7 0x0003 0x0000.019e9a14 0x0080072f 0x0000.000.00000000 0x00000001 0x00000000
0x05 10 0x80 0x00a7 0x0000 0x0000.019e9931 0x0080016e 0x0000.000.00000000 0x00000001 0x00000000
0x06 10 0x80 0x00c6 0x0004 0x0000.019ea106 0x00800849 0x0000.000.00000000 0x00000001 0x00000000
0x07 10 0x80 0x020d 0x0000 0x0000.019e9938 0x00800175 0x0000.000.00000000 0x00000001 0x00000000
0x08 10 0x80 0x00aa 0x0001 0x0000.019e99ba 0x008003a5 0x0000.000.00000000 0x00000001 0x00000000
0x09 10 0x80 0x00a5 0x0000 0x0000.019e9935 0x00800172 0x0000.000.00000000 0x00000001 0x00000000
0x0a 10 0x80 0x00a6 0x0000 0x0000.019e9937 0x00800174 0x0000.000.00000000 0x00000001 0x00000000
0x0b 10 0x80 0x00a5 0x0000 0x0000.019e9934 0x00800171 0x0000.000.00000000 0x00000001 0x00000000
0x0c 9 0x80 0x023e 0xffff 0x0000.019ea12a 0x0080084d 0x0000.000.00000000 0x00000001 0x00000000
0x0d 10 0x80 0x00a6 0x0000 0x0000.019e993b 0x00800178 0x0000.000.00000000 0x00000001 0x00000000
0x0e 10 0x80 0x00a5 0x0000 0x0000.019e9942 0x0080017c 0x0000.000.00000000 0x00000001 0x00000000
0x0f 10 0x80 0x00a5 0x0000 0x0000.019e9933 0x00800170 0x0000.000.00000000 0x00000001 0x00000000
0x10 10 0x80 0x00a5 0x0000 0x0000.019e9936 0x00800173 0x0000.000.00000000 0x00000001 0x00000000
0x11 10 0x80 0x00a7 0x0000 0x0000.019e9943 0x0080017d 0x0000.000.00000000 0x00000001 0x00000000
0x12 10 0x80 0x00db 0x0000 0x0000.019e993a 0x00800177 0x0000.000.00000000 0x00000001 0x00000000
0x13 10 0x80 0x016f 0x0000 0x0000.019e993c 0x00800179 0x0000.000.00000000 0x00000001 0x00000000
0x14 10 0x80 0x00a5 0x0000 0x0000.019e9932 0x0080016f 0x0000.000.00000000 0x00000001 0x00000000
--=================================
从dump的信息TRN TBL来看目前只有slot（0x0c,十进制数是12）可用：
接下来在session 1中：
SQL> create table t1(id int ,name varchar2(10)) tablespace users;
表已创建。
SQL> insert into t1 values(1,'a');
已创建 1 行。
SQL> insert into t1 values(1,'a');
已创建 1 行。
SQL> insert into t1 values(1,'a');
已创建 1 行。
SQL> insert into t1 values(1,'a');
已创建 1 行。
SQL> insert into t1 values(1,'a');
已创建 1 行。
SQL> commit;
提交完成。
SQL> select id,name,dbms_rowid.rowid_relative_fno(rowid) fno,dbms_rowid.rowid_bl
ock_number(rowid) bno from t1;
ID NAME FNO BNO
---------- ---------- ---------- ----------
1 a 4 2463
1 a 4 2463
1 a 4 2463
1 a 4 2463
1 a 4 2463
SQL> variable i refcursor
SQL> exec open :i for select * from t1;
PL/SQL 过程已成功完成。
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
27173099
SQL> print i;(--待执行,验证consistent read)
session 2:
SQL> select * from t1;
ID NAME
---------- ----------
1 a
1 a
1 a
1 a
1 a
SQL> update t1 set id=2 ,name='b';
已更新5行。
SQL> select sid from v$mystat where rownum=1;
SID
----------
128
SQL> select saddr,sid from v$session where sid=128;
SADDR SID
-------- ----------
6E31ABDC 128
SQL> select xidslot slot,xidsqn sqn,ubablk,ubasqn,ubarec,start_ubablk s_ubablk,s
tart_ubasqn s_ubasqn,start_ubarec,start_scn from v$transaction where ses_addr='6
E31ABDC';
SLOT SQN UBABLK UBASQN UBAREC S_UBABLK S_UBASQN START_UBAREC START_SCN
----- ----- ------- ------- ------- -------- ---------- ------------ ----------
12 706 2172 58 7 2172 58 7 27173553
SQL>
--update事务正好使用了slot 12
SQL> commit;
提交完成。
--这时候如果在session 1中执行print i的话则
需要通过slot 12来从undo中实现consistent read，这种情况
比较简单，过程如下：
当session 1中执行print i时大致的scn是：27173099
session 2中update提交时的scn是：019ea30d(从block2463
的dump信息的itl中获得)
转为10进制数：
SQL> select to_number('019ea30d','xxxxxxxx') from dual;
TO_NUMBER('019EA30D','XXXXXXXX')
--------------------------------
27173645
--===========================
SQL> alter system dump datafile 4 block 2463;
系统已更改。
--==================
Start dump data blocks tsn: 4 file#: 4 minblk 2463 maxblk 2463
buffer tsn: 4 rdba: 0x0100099f (4/2463)
scn: 0x0000.019ea30d seq: 0x01 flg: 0x06 tail: 0xa30d0601
frmt: 0x02 chkval: 0x0e0f type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0C812200 to 0x0C814200
C812200 0000A206 0100099F 019EA30D 06010000 [................]
C812210 00000E0F 00000001 00003090 019EA2B1 [.........0......]
C812220 00000000 00320002 01000999 00060002 [......2.........]
C812230 000000C3 00800846 000C003A 00008000 [....F...:.......]
C812240 019EA0C0 000C0002 000002C2 0080087C [............|...]
C812250 0007003A 00002005 019EA30D 00000000 [:.... ..........]
C812260 00000000 00050100 001CFFFF 1F4F1F70 [............p.O.]
C812270 00001F4F 1F900005 1F801F88 1F701F78 [O...........x.p.]
C812280 00000000 00000000 00000000 00000000 [................]
Repeat 500 times
C8141D0 00000000 0202022C 620103C1 0202022C [....,......b,...]
C8141E0 620103C1 0202022C 620103C1 0202022C [...b,......b,...]
C8141F0 620103C1 0202022C 620103C1 A30D0601 [...b,......b....]
Block header dump: 0x0100099f
Object id on Block? Y
seg/obj: 0x3090 csc: 0x00.19ea2b1 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1000999 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0002.006.000000c3 0x00800846.003a.0c C--- 0 scn 0x0000.019ea0c0
0x02 0x0002.00c.000002c2 0x0080087c.003a.07 --U- 5 fsc 0x0000.019ea30d
data_block_dump,data header at 0xc812264
===============
tsiz: 0x1f98
hsiz: 0x1c
pbl: 0x0c812264
bdba: 0x0100099f
76543210
flag=--------
ntab=1
nrow=5
frre=-1
fsbo=0x1c
fseo=0x1f70
avsp=0x1f4f
tosp=0x1f4f
0xe:pti[0] nrow=5 offs=0
0x12:pri[0] offs=0x1f90
0x14:pri[1] offs=0x1f88
0x16:pri[2] offs=0x1f80
0x18:pri[3] offs=0x1f78
0x1a:pri[4] offs=0x1f70
block_row_dump:
tab 0, row 0, @0x1f90
tl: 8 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 03
col 1: [ 1] 62
tab 0, row 1, @0x1f88
tl: 8 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 03
col 1: [ 1] 62
tab 0, row 2, @0x1f80
tl: 8 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 03
col 1: [ 1] 62
tab 0, row 3, @0x1f78
tl: 8 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 03
col 1: [ 1] 62
tab 0, row 4, @0x1f70
tl: 8 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 03
col 1: [ 1] 62
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 2463 maxblk 2463
--==================
提交时的scn：27173645很显然比在session 1中查询
时的scn：27173099大，因此当在session 1中执行print i时
必然要通过记录在itl中的xid到undo中的slot 12上来获得dba，这个
dba是事务使用的undo chain的单向链表的头(对应的是v$trasaction中的ubablk)
,这个单向链表的尾部可以通过itl中的uba来得到(这里的uba对用的是v$transaction中
start_ubablk),只有能找到这条链以及这条链上的undo block上记录的内容不被覆盖，那么
才能顺利的实现consistent read,否则出现ora-01555
接下来我们看看slot被覆盖的情况：
session 2:
SQL> select * from t1;
ID NAME
---------- ----------
2 b
2 b
2 b
2 b
2 b
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
27174570
SQL> delete from t1;
已删除5行。
SQL> select xidslot slot,xidsqn sqn,ubablk,ubasqn,ubarec,start_ubablk s_ubablk,s
tart_ubasqn s_ubasqn,start_ubarec,start_scn from v$transaction where ses_addr='6
E31ABDC';
SLOT SQN UBABLK UBASQN UBAREC S_UBABLK S_UBASQN START_UBAREC START_SCN
----- ----- ------- ------- ------- -------- ---------- ------------ ----------
12 968 2224 58 9 2224 58 5 27174577
SQL>
很显然slot 12被再次使用了，同时我们注意到xidsqn发生了变化
，上一次事务使用xidsqn是：706，貌似没有连续，当slot被再次使用时
sqn会增加，这就是undo中经常提到的wrap的概念。
在commit这个delete的事务之前我们来dump一下undo header：
SQL> alter system dump undo header rbs2;
系统已更改。
--==============================
Undo Segment: RBS2 (2)
********************************************************************************
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 5 #blocks: 2559
last map 0x00000000 #maps: 0 offset: 1056
Highwater:: 0x008008b0 ext#: 4 blk#: 143 ext size: 512
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 4
Unlocked
Map Header:: next 0x00000000 #extents: 5 obj#: 0 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x00800022 length: 511
0x00800221 length: 512
0x00800421 length: 512
0x00800621 length: 512
0x00800821 length: 512
TRN CTL:: seq: 0x003a chd: 0xffffffff ctl: 0xffffffff inc: 0x00000000 nfb: 0x0000
mgc: 0x8002 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x008008b0.003a.05 scn: 0x0000.019ea69c
Version: 0x01
FREE BLOCK POOL::
uba: 0x00000000.003a.04 ext: 0x4 spc: 0x5a2
uba: 0x00000000.0039.04 ext: 0x3 spc: 0x556
uba: 0x00000000.0034.04 ext: 0x0 spc: 0x5fe
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub stmt_num
------------------------------------------------------------------------------------------------
0x00 10 0x80 0x00cd 0x0000 0x0000.019e9938 0x00800176 0x0000.000.00000000 0x00000001 0x00000000
0x01 10 0x80 0x0141 0x0004 0x0000.019ea106 0x0080084a 0x0000.000.00000000 0x00000001 0x00000000
0x02 10 0x80 0x00c0 0x0000 0x0000.019e993f 0x0080017b 0x0000.000.00000000 0x00000001 0x00000000
0x03 10 0x80 0x00a7 0x0000 0x0000.019e993c 0x0080017a 0x0000.000.00000000 0x00000001 0x00000000
0x04 10 0x80 0x00d7 0x0003 0x0000.019e9a14 0x0080072f 0x0000.000.00000000 0x00000001 0x00000000
0x05 10 0x80 0x00a7 0x0000 0x0000.019e9931 0x0080016e 0x0000.000.00000000 0x00000001 0x00000000
0x06 10 0x80 0x00c6 0x0004 0x0000.019ea106 0x00800849 0x0000.000.00000000 0x00000001 0x00000000
0x07 10 0x80 0x020d 0x0000 0x0000.019e9938 0x00800175 0x0000.000.00000000 0x00000001 0x00000000
0x08 10 0x80 0x00aa 0x0001 0x0000.019e99ba 0x008003a5 0x0000.000.00000000 0x00000001 0x00000000
0x09 10 0x80 0x00a5 0x0000 0x0000.019e9935 0x00800172 0x0000.000.00000000 0x00000001 0x00000000
0x0a 10 0x80 0x00a6 0x0000 0x0000.019e9937 0x00800174 0x0000.000.00000000 0x00000001 0x00000000
0x0b 10 0x80 0x00a5 0x0000 0x0000.019e9934 0x00800171 0x0000.000.00000000 0x00000001 0x00000000
0x0c 10 0x80 0x03c8 0x0004 0x0000.019ea6b1 0x008008b0 0x0000.000.00000000 0x00000001 0x00000000
0x0d 10 0x80 0x00a6 0x0000 0x0000.019e993b 0x00800178 0x0000.000.00000000 0x00000001 0x00000000
0x0e 10 0x80 0x00a5 0x0000 0x0000.019e9942 0x0080017c 0x0000.000.00000000 0x00000001 0x00000000
0x0f 10 0x80 0x00a5 0x0000 0x0000.019e9933 0x00800170 0x0000.000.00000000 0x00000001 0x00000000
0x10 10 0x80 0x00a5 0x0000 0x0000.019e9936 0x00800173 0x0000.000.00000000 0x00000001 0x00000000
0x11 10 0x80 0x00a7 0x0000 0x0000.019e9943 0x0080017d 0x0000.000.00000000 0x00000001 0x00000000
0x12 10 0x80 0x00db 0x0000 0x0000.019e993a 0x00800177 0x0000.000.00000000 0x00000001 0x00000000
0x13 10 0x80 0x016f 0x0000 0x0000.019e993c 0x00800179 0x0000.000.00000000 0x00000001 0x00000000
0x14 10 0x80 0x00a5 0x0000 0x0000.019e9932 0x0080016f 0x0000.000.00000000 0x00000001 0x00000000
--==============================
slot被覆盖之前是否会被保存下来呢？这是我的疑问，要保存的话
又会保存在什么地方？看看是否会被保存在delete这个事务的udno block上？
dump了一下block 2224没有发现slot 12
SQL> alter system dump datafile 2 block 2224;
系统已更改。
--=========================
Start dump data blocks tsn: 5 file#: 2 minblk 2224 maxblk 2224
buffer tsn: 5 rdba: 0x008008b0 (2/2224)
scn: 0x0000.019ea6b1 seq: 0x05 flg: 0x04 tail: 0xa6b10205
frmt: 0x02 chkval: 0x93a1 type: 0x02=KTU UNDO BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0C812200 to 0x0C812A00
...........
********************************************************************************
UNDO BLK:
xid: 0x0002.00c.000003c8 seq: 0x3a cnt: 0x9 irb: 0x9 icl: 0x0 flg: 0x0000
Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset
---------------------------------------------------------------------------
0x01 0x0754 0x02 0x0674 0x03 0x0610 0x04 0x05bc 0x05 0x0510
0x06 0x04a4 0x07 0x0438 0x08 0x03cc 0x09 0x0360
*-----------------------------
* Rec #0x1 slt: 0x0c objn: 237(0x000000ed) objd: 237 tblspc: 0(0x00000000)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
uba: 0x008008af.003a.0e ctl max scn: 0x0000.019ea682 prv tx scn: 0x0000.019ea684
txn start scn: scn: 0x0000.019ea699 logon user: 0
prev brb: 8390831 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0002.00c.000003c4 uba: 0x008008af.003a.0d
flg: C--- lkc: 0 scn: 0x0000.019ea682
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0040067a hdba: 0x00400679
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 11
ncol: 19 nnew: 1 size: -7
col 5: *NULL*
*-----------------------------
* Rec #0x2 slt: 0x0c objn: 237(0x000000ed) objd: 237 tblspc: 0(0x00000000)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
uba: 0x008008b0.003a.01 ctl max scn: 0x0000.019ea684 prv tx scn: 0x0000.019ea69a
txn start scn: scn: 0x0000.019ea69b logon user: 0
prev brb: 8390832 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0002.00c.000003c5 uba: 0x008008af.003a.0e
flg: C--- lkc: 0 scn: 0x0000.019ea684
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0040067a hdba: 0x00400679
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
ncol: 19 nnew: 6 size: 7
col 4: [ 7] 78 6d 04 07 0b 23 27
col 5: [ 7] 78 6d 04 07 0b 24 2c
col 6: [ 7] 78 6d 04 07 0b 24 27
col 7: [21]
c0 02 19 4d 56 13 34 56 13 34 56 13 34 56 13 34 56 13 34 56 1a
col 9: [ 1] 80
col 10: [ 1] 80
*-----------------------------
* Rec #0x3 slt: 0x0c objn: 239(0x000000ef) objd: 239 tblspc: 0(0x00000000)
* Layer: 10 (Index) opc: 22 rci 0x02
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
index undo for leaf key operations
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0002.00c.000003c5 uba: 0x008008af.003a.10
flg: C--- lkc: 0 scn: 0x0000.019ea684
Dump kdilk : itl=2, kdxlkflg=0x1 sdc=0 indexid=0x400689 block=0x0040068a
(kdxlre): restore leaf row (clear leaf delete flags)
key :(15): 07 78 6d 04 07 0b 24 27 06 00 40 06 7a 00 00
*-----------------------------
* Rec #0x4 slt: 0x0c objn: 239(0x000000ef) objd: 239 tblspc: 0(0x00000000)
* Layer: 10 (Index) opc: 22 rci 0x03
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
index undo for leaf key operations
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x008008b0.003a.03
Dump kdilk : itl=2, kdxlkflg=0x1 sdc=196371780 indexid=0x400689 block=0x0040068a
(kdxlpu): purge leaf row
key :(15): 07 78 6d 04 07 0b 25 2c 06 00 40 06 7a 00 00
*-----------------------------
* Rec #0x5 slt: 0x0c objn: 12432(0x00003090) objd: 12432 tblspc: 4(0x00000004)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
uba: 0x008008b0.003a.02 ctl max scn: 0x0000.019ea69a prv tx scn: 0x0000.019ea69c
txn start scn: scn: 0x0000.019ea6b1 logon user: 0
prev brb: 8390832 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0002.006.000000c3 uba: 0x00800846.003a.0c
flg: C--- lkc: 0 scn: 0x0000.019ea0c0
KDO Op code: IRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0100099f hdba: 0x0100099b
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) size/delt: 8
fb: --H-FL-- lb: 0x0 cc: 2
null: --
col 0: [ 2] c1 03
col 1: [ 1] 62
*-----------------------------
* Rec #0x6 slt: 0x0c objn: 12432(0x00003090) objd: 12432 tblspc: 4(0x00000004)
* Layer: 11 (Row) opc: 1 rci 0x05
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x008008b0.003a.05
KDO Op code: IRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0100099f hdba: 0x0100099b
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 1(0x1) size/delt: 8
fb: --H-FL-- lb: 0x0 cc: 2
null: --
col 0: [ 2] c1 03
col 1: [ 1] 62
*-----------------------------
* Rec #0x7 slt: 0x0c objn: 12432(0x00003090) objd: 12432 tblspc: 4(0x00000004)
* Layer: 11 (Row) opc: 1 rci 0x06
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x008008b0.003a.06
KDO Op code: IRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0100099f hdba: 0x0100099b
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 2(0x2) size/delt: 8
fb: --H-FL-- lb: 0x0 cc: 2
null: --
col 0: [ 2] c1 03
col 1: [ 1] 62
*-----------------------------
* Rec #0x8 slt: 0x0c objn: 12432(0x00003090) objd: 12432 tblspc: 4(0x00000004)
* Layer: 11 (Row) opc: 1 rci 0x07
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x008008b0.003a.07
KDO Op code: IRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0100099f hdba: 0x0100099b
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 3(0x3) size/delt: 8
fb: --H-FL-- lb: 0x0 cc: 2
null: --
col 0: [ 2] c1 03
col 1: [ 1] 62
*-----------------------------
* Rec #0x9 slt: 0x0c objn: 12432(0x00003090) objd: 12432 tblspc: 4(0x00000004)
* Layer: 11 (Row) opc: 1 rci 0x08
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x008008b0.003a.08
KDO Op code: IRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0100099f hdba: 0x0100099b
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 4(0x4) size/delt: 8
fb: --H-FL-- lb: 0x0 cc: 2
null: --
col 0: [ 2] c1 03
col 1: [ 1] 62
End dump data blocks tsn: 5 file#: 2 minblk 2224 maxblk 2224
--=========================
update事务使用的slot 12在被覆盖之前，oracle在undo header的control scn里记下
了这个slot被覆盖时的scn：019ea69c
SQL> select to_number('019ea69c','xxxxxxxx') from dual;
TO_NUMBER('019EA69C','XXXXXXXX')
--------------------------------
27174556
SQL>
session 1查询(print i)时的scn：27173099
比control scn：27174556小，很显然这个时候slot早已经被覆盖了，
不能实现constent read 了，所以我们在执行session 1中的
print i时一定会出现ora-01555:
SQL> print i;
ID NAME
---------- ----------
1 a
1 a
1 a
1 a
1 a
SQL> print i;
奇怪的是没有出现错误，遗憾莫非是delete事务没有提交的缘故？
彻底晕了...看看事务提交之后的情况吧：
session 2：
SQL> commit;
提交完成。
session 1：
SQL> variable i refcursor
SQL> select * from t1;
未选定行
SQL> insert into t1 values(1,'a');
已创建 1 行。
SQL> commit;
提交完成。
SQL> insert into t1 values(1,'a');
已创建 1 行。
SQL> insert into t1 values(1,'a');
已创建 1 行。
SQL> insert into t1 values(1,'a');
已创建 1 行。
SQL> commit;
提交完成。
SQL> select id,name,dbms_rowid.rowid_relative_fno(rowid) fno,dbms_rowid.rowid_bl
ock_number(rowid) bno from t1;
ID NAME FNO BNO
---------- ---------- ---------- ----------
1 a 4 2463
1 a 4 2463
1 a 4 2463
1 a 4 2463
SQL> variable i refcursor
SQL> exec open :i for select * from t1;
PL/SQL 过程已成功完成。
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
27175055
SQL> print i;
session 2：
SQL> select * from t1;
ID NAME
---------- ----------
1 a
1 a
1 a
1 a
SQL> update t1 set id=2,name='b';
已更新4行。
SQL> select xidslot slot,xidsqn sqn,ubablk,ubasqn,ubarec,start_ubablk s_ubablk,s
tart_ubasqn s_ubasqn,start_ubarec,start_scn from v$transaction where ses_addr='6
E31ABDC';
SLOT SQN UBABLK UBASQN UBAREC S_UBABLK S_UBASQN START_UBAREC START_SCN
----- ----- ------- ------- ------- -------- ---------- ------------ ----------
12 977 2226 58 10 2226 58 10 27175081
SQL> commit;
提交完成。
SQL> delete from t1;
已删除4行。
SQL> select xidslot slot,xidsqn sqn,ubablk,ubasqn,ubarec,start_ubablk s_ubablk,s
tart_ubasqn s_ubasqn,start_ubarec,start_scn from v$transaction where ses_addr='6
E31ABDC';
SLOT SQN UBABLK UBASQN UBAREC S_UBABLK S_UBASQN START_UBAREC START_SCN
----- ----- ------- ------- ------- -------- ---------- ------------ ----------
12 978 2226 58 14 2226 58 11 27175091
SQL> commit;
提交完成。
SQL>
session 1:
SQL> print i;
ID NAME
---------- ----------
1 a
1 a
1 a
1 a
SQL>
依然没有出现ora-01555
更晕了........
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/19602/viewspace-1020062/ ，如需转载，请注明出处，否则将追究法律责任。
