Java开发网 - 转贴关于block中数据的存储和重组的探究

Topic: 转贴关于block中数据的存储和重组的探究

1.转贴关于block中数据的存储和重组的探究

Posted by: hzxl
Posted on: 2003-08-09 08:44

作者 biti_rainy
前言：在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

__________________
It is hard to love
It is the life

MSN: biti_rainy@hotmail.com
QQ: 3961057

oracle consultant/service

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
0xeti[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
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: pti[0] offs=0x1efa
0x14: pti[1] offs=0x1f0d
0x16: pti[2] offs=0x1f20
0x18: pti[3] offs=0x1f33
0x1a: pti[4] offs=0x1f46
0x1c: pti[5] offs=0x1f59
0x1e: pti[6] offs=0x1f6c --- 这里暂时没有发生变化
0x20: pti[7] offs=0x1f7f --- 这里暂时没有发生变化
0x22: pti[8] offs=0x1f92
0x24: pti[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 ----记录已经被删除
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

2.Re:转贴关于block中数据的存储和重组的探究(3) [Re: hzxl]

Copy to clipboard

Posted by: hzxl
Posted on: 2003-08-09 12:14

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: pti[0] offs=0x1efa
0x14: pti[1] offs=0x1f0d
0x16: pti[2] offs=0x1f20
0x18: pti[3] offs=0x1f33
0x1a: pti[4] offs=0x1f46
0x1c: pti[5] offs=0x1f59
0x1e: pti[6] offs=0x1f6c --- 这里暂时没有发生变化
0x20: pti[7] offs=0x1f7f --- 这里暂时没有发生变化
0x22: pti[8] offs=0x1f92
0x24: pti[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 ----记录已经被删除
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

Topic: 转贴 关于block中数据的存储和重组的探究

Topic: 转贴关于block中数据的存储和重组的探究