前言： 在block内部Oracle的数据到底是怎么存储的，通过rowid方式的时候又是怎样的，insert/delete/update发生的时候又是怎样的，想仔细探讨一下
先交代block里面数据的基本结构：
SQL> create table tn(a number, b varchar2(1000));
Table created.
SQL> insert into tn select rownum, 'wwweeerrrttt' from all_tables where rownum < 11;
10 rows created.
SQL> commit;
Commit complete.
SQL> exec show_space('tn');
Free Blocks.............................1
Total Blocks............................16
Total Bytes.............................131072
Unused Blocks...........................14
Unused Bytes............................114688
Last Used Ext FileId....................3
Last Used Ext BlockId...................1954
Last Used Block.........................2
SQL> alter system dump datafile 3 block 1955;
System altered.
Block header dump: 0x00c007a3
Object id on Block? Y
seg/obj: 0x66b7 csc: 0x00.1891b8a itc: 1 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 xid: 0x0001.011.000000e8 uba: 0x00803494.0147.07 --U- 10 fsc 0x0000.01891b8c
data_block_dump
===============
tsiz: 0x1fb8
hsiz: 0x26
pbl: 0x0ba76c44
bdba: 0x00c007a3
flag=-----------
ntab=1
nrow=10
frre=-1
fsbo=0x26
fseo=0x1efa
avsp=0x1ed4
tosp=0x1ed4
0xe:pti[0] nrow=10 offs=0 本块存在10条记录
0x12:pri[0] offs=0x1efa ---- 记录的起始物理位置
0x14:pri[1] offs=0x1f0d
0x16:pri[2] offs=0x1f20
0x18:pri[3] offs=0x1f33
0x1a:pri[4] offs=0x1f46
0x1c:pri[5] offs=0x1f59
0x1e:pri[6] offs=0x1f6c
0x20:pri[7] offs=0x1f7f
0x22:pri[8] offs=0x1f92
0x24:pri[9] offs=0x1fa5
block_row_dump:
tab 0, row 0, @0x1efa tl: 19 fb: --H-FL-- lb: 0x1 cc: 2 --- -- lb: 表示属于XID 0x1,cc 表示有2个字段
col 0: [ 2] c1 02 ---- 字段1 长度为2，数据为 c1 02
col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74 ---- 字段而长度 12[/COLOR]
tab 0, row 1, @0x1f0d
tl: 19 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74
tab 0, row 2, @0x1f20
tl: 19 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 04
col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74
tab 0, row 3, @0x1f33
tl: 19 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 05
col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74
tab 0, row 4, @0x1f46
tl: 19 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 06
col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74
tab 0, row 5, @0x1f59
tl: 19 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 07
col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74
tab 0, row 6, @0x1f6c
tl: 19 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 08
col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74
tab 0, row 7, @0x1f7f
tl: 19 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 09
col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74
tab 0, row 8, @0x1f92
tl: 19 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 0a
col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74
tab 0, row 9, @0x1fa5
tl: 19 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 0b
col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74
end_of_block_dump
End dump data blocks tsn: 2 file#: 3 minblk 1954 maxblk 1955
回复： 关于block数据的存储与重组
SQL> delete from tn where a =8 or a = 7;
2 rows deleted.
SQL> commit;
Commit complete.
SQL> alter system dump datafile 3 block 1955;
System altered.
删除2条记录后我们来看block中的变化
Block header dump: 0x00c007a3
Object id on Block? Y
seg/obj: 0x66b7 csc: 0x00.1891b8d itc: 1 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 xid: 0x0002.01a.000000e9 uba: 0x00800314.00d0.24 --U- 2 fsc 0x0022.01891b8f
data_block_dump
===============
tsiz: 0x1fb8
hsiz: 0x26
pbl: 0x0ba76c44
bdba: 0x00c007a3
flag=-----------
ntab=1
nrow=10
frre=-1
fsbo=0x26
fseo=0x1efa
avsp=0x1ed4
tosp=0x1efa
0xe:pti[0] nrow=10 offs=0
0x12:pri[0] offs=0x1efa
0x14:pri[1] offs=0x1f0d
0x16:pri[2] offs=0x1f20
0x18:pri[3] offs=0x1f33
0x1a:pri[4] offs=0x1f46
0x1c:pri[5] offs=0x1f59
0x1e:pri[6] offs=0x1f6c --- 这里暂时没有发生变化
0x20:pri[7] offs=0x1f7f --- 这里暂时没有发生变化[/COLOR]
0x22:pri[8] offs=0x1f92
0x24:pri[9] offs=0x1fa5
block_row_dump:
tab 0, row 0, @0x1efa
tl: 19 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 02
col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74
tab 0, row 1, @0x1f0d
tl: 19 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 03
col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74
tab 0, row 2, @0x1f20
tl: 19 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 04
col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74
tab 0, row 3, @0x1f33
tl: 19 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 05
col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74
tab 0, row 4, @0x1f46
tl: 19 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 06
col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74
tab 0, row 5, @0x1f59
tl: 19 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 07
col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74
tab 0, row 6, @0x1f6c
tl: 2 fb: --HDFL-- lb: 0x1 ----记录已经被删除
tab 0, row 7, @0x1f7f
tl: 2 fb: --HDFL-- lb: 0x1 ----记录已经被删除[/COLOR]
tab 0, row 8, @0x1f92
tl: 19 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 0a
col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74
tab 0, row 9, @0x1fa5
tl: 19 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 0b
col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74
end_of_block_dump
End dump data blocks tsn: 2 file#: 3 minblk 1955 maxblk 1955
SQL> insert into tn values(19,'q');
1 row created.
SQL> commit;
Commit complete.
SQL> alter system dump datafile 3 block 1955;
System altered.
插入一条记录，我们再看
Block header dump: 0x00c007a3
Object id on Block? Y
seg/obj: 0x66b7 csc: 0x00.1891b90 itc: 1 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 xid: 0x0003.054.000000e8 uba: 0x00800da8.00d9.19 --U- 1 fsc 0x0000.01891b91
data_block_dump
===============
tsiz: 0x1fb8
hsiz: 0x28
pbl: 0x0ba76c44
bdba: 0x00c007a3
flag=-----------
ntab=1
nrow=11
frre=6
fsbo=0x28
fseo=0x1ef2
avsp=0x1eef
tosp=0x1eef
0xe:pti[0] nrow=11 offs=0
0x12:pri[0] offs=0x1efa
0x14:pri[1] offs=0x1f0d
0x16:pri[2] offs=0x1f20
0x18:pri[3] offs=0x1f33
0x1a:pri[4] offs=0x1f46
0x1c:pri[5] offs=0x1f59
0x1e:pri[6] sfll=7 ----被删除
0x20:pri[7] sfll=-1 ------被删除
0x22:pri[8] offs=0x1f92
0x24:pri[9] offs=0x1fa5
0x26:pri[10] offs=0x1ef2 --------新插入记录
block_row_dump:
tab 0, row 0, @0x1efa
tl: 19 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 02
col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74
tab 0, row 1, @0x1f0d
tl: 19 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 03
col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74
tab 0, row 2, @0x1f20
tl: 19 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 04
col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74
tab 0, row 3, @0x1f33
tl: 19 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 05
col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74
tab 0, row 4, @0x1f46
tl: 19 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 06
col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74
tab 0, row 5, @0x1f59
tl: 19 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 07
col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74 ---- row 6,7 已经被清除
tab 0, row 8, @0x1f92
tl: 19 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 0a
col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74
tab 0, row 9, @0x1fa5
tl: 19 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 0b
col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74
tab 0, row 10, @0x1ef2 ----------------------------新插入记录
tl: 8 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 14
col 1: [ 1] 71
end_of_block_dump
End dump data blocks tsn: 2 file#: 3 minblk 1955 maxblk 1955
SQL> insert into tn values(19,'QQq');
1 row created.
SQL> commit;
Commit complete.
SQL> alter system dump datafile 3 block 1955;
System altered.
再插入记录我们来看
Block header dump: 0x00c007a3
Object id on Block? Y
seg/obj: 0x66b7 csc: 0x00.1891b92 itc: 1 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 xid: 0x0004.02e.000000e7 uba: 0x00800617.00df.1c --U- 1 fsc 0x0000.01891b94
data_block_dump
===============
tsiz: 0x1fb8
hsiz: 0x28
pbl: 0x0ba76c44
bdba: 0x00c007a3
flag=-----------
ntab=1
nrow=11
frre=7
fsbo=0x28
fseo=0x1ee8
avsp=0x1ee5
tosp=0x1ee5
0xe:pti[0] nrow=11 offs=0
0x12:pri[0] offs=0x1efa
0x14:pri[1] offs=0x1f0d
0x16:pri[2] offs=0x1f20
0x18:pri[3] offs=0x1f33
0x1a:pri[4] offs=0x1f46
0x1c:pri[5] offs=0x1f59
0x1e:pri[6] offs=0x1ee8 ------ 新插入记录使用了新的空间，注重 offs 表示物理位置
0x20:pri[7] sfll=-1
0x22:pri[8] offs=0x1f92
0x24:pri[9] offs=0x1fa5
0x26:pri[10] offs=0x1ef2
block_row_dump:
tab 0, row 0, @0x1efa
tl: 19 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 02
col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74
tab 0, row 1, @0x1f0d
tl: 19 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 03
col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74
tab 0, row 2, @0x1f20
tl: 19 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 04
col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74
tab 0, row 3, @0x1f33
tl: 19 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 05
col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74
tab 0, row 4, @0x1f46
tl: 19 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 06
col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74
tab 0, row 5, @0x1f59
tl: 19 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 07
col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74
tab 0, row 6, @0x1ee8 ------------------------新插入记录
tl: 10 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 14
col 1: [ 3] 71 71 71
tab 0, row 8, @0x1f92
tl: 19 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 0a
col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74
tab 0, row 9, @0x1fa5
tl: 19 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 0b
col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74
tab 0, row 10, @0x1ef2
tl: 8 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 14
col 1: [ 1] 71
end_of_block_dump
End dump data blocks tsn: 2 file#: 3 minblk 1955 maxblk 1955
SQL> update tn set b = 'qqqqqq' where a = 19;
2 rows updated.
SQL> commit;
Commit complete.
SQL> alter system dump datafile 3 block 1955;
System altered.
更新新插入的两条记录
Block header dump: 0x00c007a3
Object id on Block? Y
seg/obj: 0x66b7 csc: 0x00.1891b95 itc: 1 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 xid: 0x0005.047.000000e7 uba: 0x00803819.0154.04 --U- 2 fsc 0x0000.01891b97
data_block_dump
===============
tsiz: 0x1fb8
hsiz: 0x28
pbl: 0x0ba76c44
bdba: 0x00c007a3
flag=-----------
ntab=1
nrow=11
frre=7
fsbo=0x28
fseo=0x1ece
avsp=0x1ede
tosp=0x1ede
0xe:pti[0] nrow=11 offs=0
0x12:pri[0] offs=0x1efa
0x14:pri[1] offs=0x1f0d
0x16:pri[2] offs=0x1f20
0x18:pri[3] offs=0x1f33
0x1a:pri[4] offs=0x1f46
0x1c:pri[5] offs=0x1f59
0x1e:pri[6] offs=0x1edb --------首先更新这条， 更新后由于原来地方空间不足，被挪到了0x26:pri[10] offs=0x1ef2 之上
0x20:pri[7] sfll=-1
0x22:pri[8] offs=0x1f92
0x24:pri[9] offs=0x1fa5
0x26:pri[10] offs=0x1ece ------- 更新后又由于先更新了0x1e:pri[6],位置不足，又挪到了0x1ece
block_row_dump:
tab 0, row 0, @0x1efa
tl: 19 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 02
col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74
tab 0, row 1, @0x1f0d
tl: 19 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 03
col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74
tab 0, row 2, @0x1f20
tl: 19 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 04
col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74
tab 0, row 3, @0x1f33
tl: 19 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 05
col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74
tab 0, row 4, @0x1f46
tl: 19 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 06
col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74
tab 0, row 5, @0x1f59
tl: 19 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 07
col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74
tab 0, row 6, @0x1edb
tl: 13 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 14
col 1: [ 6] 71 71 71 71 71 71
tab 0, row 8, @0x1f92
tl: 19 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 0a
col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74
tab 0, row 9, @0x1fa5
tl: 19 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 0b
col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74
tab 0, row 10, @0x1ece
tl: 13 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 14
col 1: [ 6] 71 71 71 71 71 71
end_of_block_dump
End dump data blocks tsn: 2 file#: 3 minblk 1955 maxblk 1955
SQL> update tn set b = 'qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq
qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq' where a = 19;
2 rows updated.
SQL> commit;
Commit complete.
SQL> alter system dump datafile 3 block 1955;
System altered.
|
