前言： 在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.............................1Total Blocks............................16Total Bytes.............................131072Unused Blocks...........................14Unused Bytes............................114688Last Used Ext FileId....................3Last Used Ext BlockId...................1954Last 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/Fsc0x01 xid: 0x0001.011.000000e8 uba: 0x00803494.0147.07 --U- 10 fsc 0x0000.01891b8c data_block_dump===============tsiz: 0x1fb8hsiz: 0x26pbl: 0x0ba76c44bdba: 0x00c007a3flag=-----------ntab=1nrow=10frre=-1fsbo=0x26fSEO=0x1efaavsp=0x1ed4tosp=0x1ed40xe:pti[0] nrow=10 offs=0 本塊存在10條記錄0x12:PRi[0] offs=0x1efa ---- 記錄的起始物理位置0x14:pri[1] offs=0x1f0d0x16:pri[2] offs=0x1f200x18:pri[3] offs=0x1f330x1a:pri[4] offs=0x1f460x1c:pri[5] offs=0x1f590x1e:pri[6] offs=0x1f6c0x20:pri[7] offs=0x1f7f0x22:pri[8] offs=0x1f920x24:pri[9] offs=0x1fa5block_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 02col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74 ---- 字段而長度 12[/COLOR]tab 0, row 1, @0x1f0dtl: 19 fb: --H-FL-- lb: 0x1 cc: 2col 0: [ 2] c1 03col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74tab 0, row 2, @0x1f20tl: 19 fb: --H-FL-- lb: 0x1 cc: 2col 0: [ 2] c1 04col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74tab 0, row 3, @0x1f33tl: 19 fb: --H-FL-- lb: 0x1 cc: 2col 0: [ 2] c1 05col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74tab 0, row 4, @0x1f46tl: 19 fb: --H-FL-- lb: 0x1 cc: 2col 0: [ 2] c1 06col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74tab 0, row 5, @0x1f59tl: 19 fb: --H-FL-- lb: 0x1 cc: 2col 0: [ 2] c1 07col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74tab 0, row 6, @0x1f6ctl: 19 fb: --H-FL-- lb: 0x1 cc: 2col 0: [ 2] c1 08col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74tab 0, row 7, @0x1f7ftl: 19 fb: --H-FL-- lb: 0x1 cc: 2col 0: [ 2] c1 09col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74tab 0, row 8, @0x1f92tl: 19 fb: --H-FL-- lb: 0x1 cc: 2col 0: [ 2] c1 0acol 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74tab 0, row 9, @0x1fa5tl: 19 fb: --H-FL-- lb: 0x1 cc: 2col 0: [ 2] c1 0bcol 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74end_of_block_dumpEnd 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/Fsc0x01 xid: 0x0002.01a.000000e9 uba: 0x00800314.00d0.24 --U- 2 fsc 0x0022.01891b8f data_block_dump===============tsiz: 0x1fb8hsiz: 0x26pbl: 0x0ba76c44bdba: 0x00c007a3flag=-----------ntab=1nrow=10frre=-1fsbo=0x26fseo=0x1efaavsp=0x1ed4tosp=0x1efa0xe:pti[0] nrow=10 offs=00x12:pri[0] offs=0x1efa0x14:pri[1] offs=0x1f0d0x16:pri[2] offs=0x1f200x18:pri[3] offs=0x1f330x1a:pri[4] offs=0x1f460x1c:pri[5] offs=0x1f590x1e:pri[6] offs=0x1f6c --- 這里暫時沒有發生變化0x20:pri[7] offs=0x1f7f --- 這里暫時沒有發生變化[/COLOR]0x22:pri[8] offs=0x1f920x24:pri[9] offs=0x1fa5block_row_dump:tab 0, row 0, @0x1efatl: 19 fb: --H-FL-- lb: 0x0 cc: 2col 0: [ 2] c1 02col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74tab 0, row 1, @0x1f0dtl: 19 fb: --H-FL-- lb: 0x0 cc: 2col 0: [ 2] c1 03col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74tab 0, row 2, @0x1f20tl: 19 fb: --H-FL-- lb: 0x0 cc: 2col 0: [ 2] c1 04col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74tab 0, row 3, @0x1f33tl: 19 fb: --H-FL-- lb: 0x0 cc: 2col 0: [ 2] c1 05col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74tab 0, row 4, @0x1f46tl: 19 fb: --H-FL-- lb: 0x0 cc: 2col 0: [ 2] c1 06col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74tab 0, row 5, @0x1f59tl: 19 fb: --H-FL-- lb: 0x0 cc: 2col 0: [ 2] c1 07col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74tab 0, row 6, @0x1f6ctl: 2 fb: --HDFL-- lb: 0x1 ----記錄已經被刪除tab 0, row 7, @0x1f7ftl: 2 fb: --HDFL-- lb: 0x1 ----記錄已經被刪除[/COLOR]tab 0, row 8, @0x1f92tl: 19 fb: --H-FL-- lb: 0x0 cc: 2col 0: [ 2] c1 0acol 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74tab 0, row 9, @0x1fa5tl: 19 fb: --H-FL-- lb: 0x0 cc: 2col 0: [ 2] c1 0bcol 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74end_of_block_dumpEnd 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/Fsc0x01 xid: 0x0003.054.000000e8 uba: 0x00800da8.00d9.19 --U- 1 fsc 0x0000.01891b91 data_block_dump===============tsiz: 0x1fb8hsiz: 0x28pbl: 0x0ba76c44bdba: 0x00c007a3flag=-----------ntab=1nrow=11frre=6fsbo=0x28fseo=0x1ef2avsp=0x1eeftosp=0x1eef0xe:pti[0] nrow=11 offs=00x12:pri[0] offs=0x1efa0x14:pri[1] offs=0x1f0d0x16:pri[2] offs=0x1f200x18:pri[3] offs=0x1f330x1a:pri[4] offs=0x1f460x1c:pri[5] offs=0x1f590x1e:pri[6] sfll=7 ----被刪除0x20:pri[7] sfll=-1 ------被刪除0x22:pri[8] offs=0x1f920x24:pri[9] offs=0x1fa50x26:pri[10] offs=0x1ef2 --------新插入記錄block_row_dump:tab 0, row 0, @0x1efatl: 19 fb: --H-FL-- lb: 0x0 cc: 2col 0: [ 2] c1 02col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74tab 0, row 1, @0x1f0dtl: 19 fb: --H-FL-- lb: 0x0 cc: 2col 0: [ 2] c1 03col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74tab 0, row 2, @0x1f20tl: 19 fb: --H-FL-- lb: 0x0 cc: 2col 0: [ 2] c1 04col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74tab 0, row 3, @0x1f33tl: 19 fb: --H-FL-- lb: 0x0 cc: 2col 0: [ 2] c1 05col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74tab 0, row 4, @0x1f46tl: 19 fb: --H-FL-- lb: 0x0 cc: 2col 0: [ 2] c1 06col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74tab 0, row 5, @0x1f59tl: 19 fb: --H-FL-- lb: 0x0 cc: 2col 0: [ 2] c1 07col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74 ---- row 6,7 已經被清除tab 0, row 8, @0x1f92tl: 19 fb: --H-FL-- lb: 0x0 cc: 2col 0: [ 2] c1 0acol 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74tab 0, row 9, @0x1fa5tl: 19 fb: --H-FL-- lb: 0x0 cc: 2col 0: [ 2] c1 0bcol 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74tab 0, row 10, @0x1ef2 ----------------------------新插入記錄tl: 8 fb: --H-FL-- lb: 0x1 cc: 2col 0: [ 2] c1 14col 1: [ 1] 71end_of_block_dumpEnd 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/Fsc0x01 xid: 0x0004.02e.000000e7 uba: 0x00800617.00df.1c --U- 1 fsc 0x0000.01891b94 data_block_dump===============tsiz: 0x1fb8hsiz: 0x28pbl: 0x0ba76c44bdba: 0x00c007a3flag=-----------ntab=1nrow=11frre=7fsbo=0x28fseo=0x1ee8avsp=0x1ee5tosp=0x1ee50xe:pti[0] nrow=11 offs=00x12:pri[0] offs=0x1efa0x14:pri[1] offs=0x1f0d0x16:pri[2] offs=0x1f200x18:pri[3] offs=0x1f330x1a:pri[4] offs=0x1f460x1c:pri[5] offs=0x1f590x1e:pri[6] offs=0x1ee8 ------ 新插入記錄使用了新的空間，注重 offs 表示物理位置0x20:pri[7] sfll=-10x22:pri[8] offs=0x1f920x24:pri[9] offs=0x1fa50x26:pri[10] offs=0x1ef2block_row_dump:tab 0, row 0, @0x1efatl: 19 fb: --H-FL-- lb: 0x0 cc: 2col 0: [ 2] c1 02col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74tab 0, row 1, @0x1f0dtl: 19 fb: --H-FL-- lb: 0x0 cc: 2col 0: [ 2] c1 03col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74tab 0, row 2, @0x1f20tl: 19 fb: --H-FL-- lb: 0x0 cc: 2col 0: [ 2] c1 04col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74tab 0, row 3, @0x1f33tl: 19 fb: --H-FL-- lb: 0x0 cc: 2col 0: [ 2] c1 05col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74tab 0, row 4, @0x1f46tl: 19 fb: --H-FL-- lb: 0x0 cc: 2col 0: [ 2] c1 06col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74tab 0, row 5, @0x1f59tl: 19 fb: --H-FL-- lb: 0x0 cc: 2col 0: [ 2] c1 07col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74tab 0, row 6, @0x1ee8 ------------------------新插入記錄tl: 10 fb: --H-FL-- lb: 0x1 cc: 2col 0: [ 2] c1 14col 1: [ 3] 71 71 71tab 0, row 8, @0x1f92tl: 19 fb: --H-FL-- lb: 0x0 cc: 2col 0: [ 2] c1 0acol 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74tab 0, row 9, @0x1fa5tl: 19 fb: --H-FL-- lb: 0x0 cc: 2col 0: [ 2] c1 0bcol 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74tab 0, row 10, @0x1ef2tl: 8 fb: --H-FL-- lb: 0x0 cc: 2col 0: [ 2] c1 14col 1: [ 1] 71end_of_block_dumpEnd 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/Fsc0x01 xid: 0x0005.047.000000e7 uba: 0x00803819.0154.04 --U- 2 fsc 0x0000.01891b97 data_block_dump===============tsiz: 0x1fb8hsiz: 0x28pbl: 0x0ba76c44bdba: 0x00c007a3flag=-----------ntab=1nrow=11frre=7fsbo=0x28fseo=0x1eceavsp=0x1edetosp=0x1ede0xe:pti[0] nrow=11 offs=00x12:pri[0] offs=0x1efa0x14:pri[1] offs=0x1f0d0x16:pri[2] offs=0x1f200x18:pri[3] offs=0x1f330x1a:pri[4] offs=0x1f460x1c:pri[5] offs=0x1f590x1e:pri[6] offs=0x1edb --------首先更新這條， 更新后由于原來地方空間不足，被挪到了0x26:pri[10] offs=0x1ef2 之上0x20:pri[7] sfll=-10x22:pri[8] offs=0x1f920x24:pri[9] offs=0x1fa50x26:pri[10] offs=0x1ece ------- 更新后又由于先更新了0x1e:pri[6],位置不足，又挪到了0x1eceblock_row_dump:tab 0, row 0, @0x1efatl: 19 fb: --H-FL-- lb: 0x0 cc: 2col 0: [ 2] c1 02col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74tab 0, row 1, @0x1f0dtl: 19 fb: --H-FL-- lb: 0x0 cc: 2col 0: [ 2] c1 03col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74tab 0, row 2, @0x1f20tl: 19 fb: --H-FL-- lb: 0x0 cc: 2col 0: [ 2] c1 04col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74tab 0, row 3, @0x1f33tl: 19 fb: --H-FL-- lb: 0x0 cc: 2col 0: [ 2] c1 05col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74tab 0, row 4, @0x1f46tl: 19 fb: --H-FL-- lb: 0x0 cc: 2col 0: [ 2] c1 06col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74tab 0, row 5, @0x1f59tl: 19 fb: --H-FL-- lb: 0x0 cc: 2col 0: [ 2] c1 07col 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74tab 0, row 6, @0x1edbtl: 13 fb: --H-FL-- lb: 0x1 cc: 2col 0: [ 2] c1 14col 1: [ 6] 71 71 71 71 71 71tab 0, row 8, @0x1f92tl: 19 fb: --H-FL-- lb: 0x0 cc: 2col 0: [ 2] c1 0acol 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74tab 0, row 9, @0x1fa5tl: 19 fb: --H-FL-- lb: 0x0 cc: 2col 0: [ 2] c1 0bcol 1: [12] 77 77 77 65 65 65 72 72 72 74 74 74tab 0, row 10, @0x1ecetl: 13 fb: --H-FL-- lb: 0x1 cc: 2col 0: [ 2] c1 14col 1: [ 6] 71 71 71 71 71 71end_of_block_dumpEnd dump data blocks tsn: 2 file#: 3 minblk 1955 maxblk 1955 SQL> update tn set b = 'qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq' where a = 19; 2 rows updated. SQL> commit; Commit complete. SQL> alter system dump datafile 3 block 1955; System altered.
