前言： 在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
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=0x1f