关于block中行数据的存储与空间重组一_Oracle教程

前言：在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.