前言: 在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 block1955;
System altered.
Block header dump:0x00c007a3
Object id on Block? Y
seg/obj: 0x66b7csc: 0x00.1891b8aitc: 1flg: Otyp: 1 - DATA
fsl: 0fnx: 0x0 ver: 0x01
Itl XidUba FlagLckScn/Fsc
0x01 xid:0x0001.011.000000e8uba: 0x00803494.0147.07--U- 10fsc 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=10offs=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个字段
col0: [ 2]c1 02 ---- 字段1 长度为2,数据为 c102
col1: [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
col0: [ 2]c1 03
col1: [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
col0: [ 2]c1 04
col1: [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
col0: [ 2]c1 05
col1: [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
col0: [ 2]c1 06
col1: [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
col0: [ 2]c1 07
col1: [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
col0: [ 2]c1 08
col1: [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
col0: [ 2]c1 09
col1: [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
col0: [ 2]c1 0a
col1: [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
col0: [ 2]c1 0b
col1: [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 ora = 7;
2 rows deleted.
SQL commit;
Commit complete.
SQLalter system dump datafile 3 block 1955;
System altered.
删除2条记录后我们来看block中的变化
Block header dump:0x00c007a3
Object id on Block?
Y
seg/obj: 0x66b7csc: 0x00.1891b8ditc: 1flg: Otyp: 1 - DATA
fsl: 0fnx: 0x0 ver: 0x01
Itl XidUba FlagLckScn/Fsc
0x01 xid:0x0002.01a.000000e9uba: 0x00800314.00d0.24--U-2fsc 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=10offs=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
col0: [ 2]c1 02
col1: [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
col0: [ 2]c1 03
col1: [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
col0: [ 2]c1 04
col1: [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
col0: [ 2]c1 05
col1: [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
col0: [ 2]c1 06
col1: [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
col0: [ 2]c1 07
col1: [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
col0: [ 2]c1 0a
col1: [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
col0: [ 2]c1 0b
col1: [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
SQLinsert into tn values(19,'q');
1 row created.
SQL commit;
Commit complete.
SQLalter system dump datafile 3 block 1955;
System altered.
插入一条记录,我们再看
Block header dump:0x00c007a3
Object id on Block? Y
seg/obj: 0x66b7csc: 0x00.1891b90itc: 1flg: Otyp: 1 - DATA
fsl: 0fnx: 0x0 ver: 0x01
Itl XidUba FlagLckScn/Fsc
0x01 xid:0x0003.054.000000e8uba: 0x00800da8.00d9.19--U-1fsc 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=11offs=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
right"(出处:清风软件下载学院)