关于block中行数据的存储与空间重组二

继续更新一次看看，又重复了上面的步骤，

提升了 物理位置

Block header dump: 0x00c007a3

Object id on Block? Y

seg/obj: 0x66b7 csc: 0x00.1891b98 itc: 1 flg: O typ: 1 - DATA

fsl: 0 fnx: 0x0 ver: 0x01

Itl Xid Uba Flag Lck Scn/Fsc

0x01 xid: 0x0006.044.000000f2 uba: 0x00801660.00da.0f --U- 2 fsc 0x0000.01891b99

data_block_dump

===============

tsiz: 0x1fb8

hsiz: 0x28

pbl: 0x0ba76c44

bdba: 0x00c007a3

flag=-----------

ntab=1

nrow=11

frre=7

fsbo=0x28

fseo=0x1de0

avsp=0x1e0a

tosp=0x1e0a

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=0x1e57 -------发生变化

0x20:pri[7] sfll=-1

0x22:pri[8] offs=0x1f92

0x24:pri[9] offs=0x1fa5

0x26:pri[10] offs=0x1de0 ------ 发生变化

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, @0x1e57

tl: 119 fb: --H-FL-- lb: 0x1 cc: 2

col 0: [ 2] c1 14

col 1: [112] ---------------数据长度大大增加

71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71

71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71

71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71

71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71

71 71 71 71 71 71 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, @0x1de0

tl: 119 fb: --H-FL-- lb: 0x1 cc: 2

col 0: [ 2] c1 14

col 1: [112] ---------------数据长度大大增加

71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71

71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71

71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71

71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71

71 71 71 71 71 71 71 71 71 71 71 71

end_of_block_dump

End dump data blocks tsn: 2 file#: 3 minblk 1955 maxblk 1955

SQL> alter table tn add ( c varchar2(30));

Table altered.

SQL> alter system dump datafile 3 block 1955;

System altered.

给表增加一个字段，我们发现数据没有变化

Block header dump: 0x00c007a3

Object id on Block? Y

seg/obj: 0x66b7 csc: 0x00.1891b98 itc: 1 flg: O typ: 1 - DATA

fsl: 0 fnx: 0x0 ver: 0x01

Itl Xid Uba Flag Lck Scn/Fsc

0x01 xid: 0x0006.044.000000f2 uba: 0x00801660.00da.0f --U- 2 fsc 0x0000.01891b99

data_block_dump

===============

tsiz: 0x1fb8

hsiz: 0x28

pbl: 0x0ba76c44

bdba: 0x00c007a3

flag=-----------

ntab=1

nrow=11

frre=7

fsbo=0x28

fseo=0x1de0

avsp=0x1e0a

tosp=0x1e0a

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=0x1e57

0x20:pri[7] sfll=-1

0x22:pri[8] offs=0x1f92

0x24:pri[9] offs=0x1fa5

0x26:pri[10] offs=0x1de0

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, @0x1e57

tl: 119 fb: --H-FL-- lb: 0x1 cc: 2

col 0: [ 2] c1 14

col 1: [112]

71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71

71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71

71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71

71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71

71 71 71 71 71 71 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, @0x1de0

tl: 119 fb: --H-FL-- lb: 0x1 cc: 2

col 0: [ 2] c1 14

col 1: [112]

71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71

71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71

71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71

71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71

71 71 71 71 71 71 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 c = 'p';

10 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.1891b9c itc: 1 flg: O typ: 1 - DATA

fsl: 0 fnx: 0x0 ver: 0x01

Itl Xid Uba Flag Lck Scn/Fsc

0x01 xid: 0x0001.00a.000000e8 uba: 0x00803494.0147.11 --U- 10 fsc 0x0000.01891b9e

data_block_dump

===============

tsiz: 0x1fb8

hsiz: 0x28

pbl: 0x0ba76c44

bdba: 0x00c007a3

flag=-----------

ntab=1

nrow=11

frre=7

fsbo=0x28

fseo=0x1c46

avsp=0x1df6

tosp=0x1df6

0xe:pti[0] nrow=11 offs=0

0x12:pri[0] offs=0x1dcb ---------所有行的物理位置都发生了变化,因为原来的位置装不下多出来的数据了

0x14:pri[1] offs=0x1db6

0x16:pri[2] offs=0x1da1

0x18:pri[3] offs=0x1d8c

0x1a:pri[4] offs=0x1d77

0x1c:pri[5] offs=0x1d62

0x1e:pri[6] offs=0x1ce9

0x20:pri[7] sfll=-1

0x22:pri[8] offs=0x1cd4

0x24:pri[9] offs=0x1cbf

0x26:pri[10] offs=0x1c46

block_row_dump:

tab 0, row 0, @0x1dcb

tl: 21 fb: --H-FL-- lb: 0x1 cc: 3

col 0: [ 2] c1 02

col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74

col 2: [ 1] 70

tab 0, row 1, @0x1db6

tl: 21 fb: --H-FL-- lb: 0x1 cc: 3

col 0: [ 2] c1 03

col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74

col 2: [ 1] 70

tab 0, row 2, @0x1da1

tl: 21 fb: --H-FL-- lb: 0x1 cc: 3

col 0: [ 2] c1 04

col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74

col 2: [ 1] 70

tab 0, row 3, @0x1d8c

tl: 21 fb: --H-FL-- lb: 0x1 cc: 3

col 0: [ 2] c1 05

col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74

col 2: [ 1] 70

tab 0, row 4, @0x1d77

tl: 21 fb: --H-FL-- lb: 0x1 cc: 3

col 0: [ 2] c1 06

col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74

col 2: [ 1] 70

tab 0, row 5, @0x1d62

tl: 21 fb: --H-FL-- lb: 0x1 cc: 3

col 0: [ 2] c1 07

col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74

col 2: [ 1] 70

tab 0, row 6, @0x1ce9

tl: 121 fb: --H-FL-- lb: 0x1 cc: 3

col 0: [ 2] c1 14

col 1: [112]

71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71

71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71

71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71

71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71

71 71 71 71 71 71 71 71 71 71 71 71

col 2: [ 1] 70

tab 0, row 8, @0x1cd4

tl: 21 fb: --H-FL-- lb: 0x1 cc: 3

col 0: [ 2] c1 0a

col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74

col 2: [ 1] 70

tab 0, row 9, @0x1cbf

tl: 21 fb: --H-FL-- lb: 0x1 cc: 3

col 0: [ 2] c1 0b

col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74

col 2: [ 1] 70

tab 0, row 10, @0x1c46

tl: 121 fb: --H-FL-- lb: 0x1 cc: 3

col 0: [ 2] c1 14

col 1: [112]

71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71

71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71

71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71

71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71

71 71 71 71 71 71 71 71 71 71 71 71

col 2: [ 1] 70

end_of_block_dump

End dump data blocks tsn: 2 file#: 3 minblk 1955 maxblk 1955

right"（出处：清风软件下载学院）

• ·Oracle和SQL Server存储之调试及
• ·oracle 备份数据库
• ·从minimize records_per_b
• ·用RMAN自动备份脚本，且自动拷贝到其它win
• ·数据文件internal分析
• ·在EXP出数据时遇到ORA-00942的问题的
• ·9i新特性之——在线表格重定义研究1
• ·9i新特性之——在线表格重定义研究2
• ·在Windows下管理Oracle9i 服务的
• ·数据库名DB_name实例名instance全