ORACLE關于段的HEADER_BLOCK的一點淺析

在學習段（segment）、區間（extent）時，對段的HEADER_BLOCK有一些疑問，本文記錄一下探究的實驗過程以及相關總結，，如有不對的地方，敬請指出。以SCOTT.EMP表為例（下面測試環境為Oracle Database 10g Release 10.2.0.5.0 - 64bit Production）：

SELECT FILE_ID, ?????? BLOCK_ID, ?????? BLOCKS FROM?? DBA_EXTENTS WHERE? OWNER ='&OWNER'???? AND SEGMENT_NAME = '&TABLE_NAME'; 

SELECT HEADER_FILE???? , HEADER_BLOCK???? , BYTES???? , BLOCKS???? , EXTENTS FROM DBA_SEGMENTS WHERE OWNER='&OWNER'?AND SEGMENT_NAME='&SEGMENT_NAME';

如上所示，DBA_SEGMENTS 中的HEADER_BLOCK 與DBA_EXTENTS的BLOCK_ID不同（HEADER_BLOCK：文件ID為4的第27個塊，區間的第一個塊的BLOCK_ID為第25個塊），這個的原因如下：

一個segment的第一個區的第一個塊是FIRST LEVEL BITMAP BLOCK，第二個塊是SECOND LEVEL BITMAP BLOCK，這兩個塊是用來管理free block的，第三個塊是PAGETABLE SEGMENT HEADER，這個塊才是segment里的HEADER_BLOCK，再后面的塊就是用來記錄數據的。所以25+2=27. 詳細可以參考《循序漸進ORCLE:數據庫管理、優化與備份》這本書的第5章。

下面我們創建一個表，測試一下是否也是這個規律，如下所示：

SQL> CREATE?TABLE TEST1.MMM??? ? 2? AS? 3? SELECT * FROM DBA_OBJECTS;?Table created.?SQL> COL SEGMENT_NAME FOR A32;SQL> SELECT SEGMENT_NAME? 2???????? ,FILE_ID? 3???????? ,BLOCK_ID? 4???????? ,BLOCKS? 5? FROM DBA_EXTENTS ? 6? WHERE SEGMENT_NAME='MMM'?AND OWNER='TEST1'? 7? ORDER?BY BLOCK_ID ASC;?SEGMENT_NAME??????????????????????? FILE_ID?? BLOCK_ID???? BLOCKS-------------------------------- ---------- ---------- ----------MMM????????????????????????????????????? 76????????? 9????????? 8MMM????????????????????????????????????? 76???????? 17????????? 8MMM????????????????????????????????????? 76???????? 25????????? 8MMM????????????????????????????????????? 76???????? 33????????? 8MMM????????????????????????????????????? 76???????? 41????????? 8MMM????????????????????????????????????? 76???????? 49????????? 8MMM????????????????????????????????????? 76???????? 57????????? 8MMM????????????????????????????????????? 76???????? 65????????? 8MMM????????????????????????????????????? 76???????? 73????????? 8MMM????????????????????????????????????? 76???????? 81????????? 8MMM????????????????????????????????????? 76???????? 89????????? 8?SEGMENT_NAME??????????????????????? FILE_ID?? BLOCK_ID???? BLOCKS-------------------------------- ---------- ---------- ----------MMM????????????????????????????????????? 76???????? 97????????? 8MMM????????????????????????????????????? 76??????? 105????????? 8MMM????????????????????????????????????? 76??????? 113????????? 8MMM????????????????????????????????????? 76??????? 121????????? 8MMM????????????????????????????????????? 76??????? 129????????? 8MMM????????????????????????????????????? 76??????? 137??????? 128MMM????????????????????????????????????? 76??????? 265??????? 128MMM????????????????????????????????????? 76??????? 393??????? 128MMM????????????????????????????????????? 76??????? 521??????? 128MMM????????????????????????????????????? 76??????? 649??????? 128MMM????????????????????????????????????? 76??????? 777??????? 128?22 rows selected.?SQL> SELECT HEADER_FILE? 2?????? , HEADER_BLOCK? 3?????? , BYTES? 4?????? , BLOCKS? 5?????? , EXTENTS ? 6? FROM DBA_SEGMENTS ? 7? WHERE OWNER='TEST1'?AND SEGMENT_NAME='MMM';?HEADER_FILE HEADER_BLOCK????? BYTES???? BLOCKS??? EXTENTS----------- ------------ ---------- ---------- ----------???????? 76?????????? 11??? 7340032??????? 896???????? 22

如上所示，段對象TEST1.MMM的header_block為11 ，而對應的區間的第一個塊對象ID為9，也是9+2=11，確實是如此，那么我們來DUMP數據塊看看，如下所示

SQL> alter system dump datafile 76 block 9;?System altered.?SQL> alter system dump datafile 76 block 10;?System altered.?SQL> alter system dump datafile 76 block 11;?System altered.?SQL> select user_dump.value?? 2???????? || '/'?? 3???????? || lower(instance.value) ? 4???????? || '_ora_'?? 5???????? || v$process.spid ? 6???????? || nvl2(v$process.traceid, '_'?? 7??????????????????????????????????? || v$process.traceid, null) ? 8???????? || '.trc'"trace file" ? 9? from?? v$parameter user_dump  10???????? cross?join v$parameter instance  11???????? cross?join v$process  12???????? join v$session? 13?????????? on v$process.addr = v$session.paddr  14? where? user_dump.name = 'user_dump_dest'? 15???????? and instance.name = 'instance_name'? 16???????? and v$session.audsid = sys_context('userenv', 'sessionid');? ?trace file--------------------------------------------------------------------------------/u01/app/oracle/admin/SCM2/udump/scm2_ora_22642.trc

第一個區的第一個塊（block_id=9)是FIRST LEVEL BITMAP BLOCK，第二個塊(block_id=10)是SECOND LEVEL BITMAP BLOCK，這兩個塊是用來管理free block的，第三個塊(block_id=11)是PAGETABLE SEGMENT HEADER，這個塊才是segment里的HEADER_BLOCK，再后面的塊就是用來記錄數據的

不過有一個奇怪的現象，對SCOTT.EMP其數據塊做dump，發現25、26、27數據塊的type都是trans data，0x06表示的Block Type為 Table/cluster/index segment data block 。 不知是否因為SCOTT.EMP對象位于USERS表空間下的緣故。不過USER表空間也是ASSM管理的。具體情況尚不清楚？

SQL> SELECT TABLESPACE_NAME? 2?????? , SEGMENT_SPACE_MANAGEMENT? 3?????? , ALLOCATION_TYPE? 4?????? , EXTENT_MANAGEMENT? 5? FROM DBA_TABLESPACES? 6? WHERE TABLESPACE_NAME='USERS';?TABLESPACE_NAME??????????????? SEGMEN ALLOCATIO EXTENT_MAN------------------------------ ------ --------- ----------USERS????????????????????????? AUTO?? SYSTEM??? LOCAL

那么是否所有的HEADER_BLOCK都是位于段的第三個block呢？是否還跟段空間管理的方式有關呢？我們用如下實驗來探究一下：創建一個手工段空間管理（Manual Segment Space Management）的表空間。

SQL> CREATE TABLESPACE TBS_TEST_DATA? 2? DATAFILE '/u03/oradata/gsp/tbs_test_data_001.dbf'? 3? SIZE 20M ? 4? EXTENT MANAGEMENT LOCAL AUTOALLOCATE? 5? SEGMENT SPACE MANAGEMENT MANUAL ONLINE;?Tablespace created.??SQL> create?user test identified by test123456? 2? default tablespace tbs_test_data;?User created.?SQL> grant?connect, resource to test;?Grant succeeded.?SQL> CREATE?TABLE TEST.KKK??? ? 2? AS? 3? SELECT * FROM DBA_OBJECTS;?Table created.?SQL> COL SEGMENT_NAME FOR A32;SQL> SELECT SEGMENT_NAME? 2??????? ,FILE_ID? 3??????? ,BLOCK_ID? 4??????? ,BLOCKS? 5? FROM DBA_EXTENTS ? 6? WHERE SEGMENT_NAME='KKK'?AND OWNER='TEST'? 7? ORDER?BY BLOCK_ID ASC;?SEGMENT_NAME??????????????????????? FILE_ID?? BLOCK_ID???? BLOCKS-------------------------------- ---------- ---------- ----------KKK????????????????????????????????????? 39???? 427785??????? 128KKK????????????????????????????????????? 43???? 435249????????? 8KKK????????????????????????????????????? 43???? 435257????????? 8KKK????????????????????????????????????? 43???? 435265????????? 8KKK????????????????????????????????????? 43???? 435273????????? 8KKK????????????????????????????????????? 43???? 435281????????? 8KKK????????????????????????????????????? 43???? 435289????????? 8KKK????????????????????????????????????? 43???? 435297????????? 8KKK????????????????????????????????????? 43???? 435305????????? 8KKK????????????????????????????????????? 43???? 435313????????? 8KKK????????????????????????????????????? 43???? 435321????????? 8?SEGMENT_NAME??????????????????????? FILE_ID?? BLOCK_ID???? BLOCKS-------------------------------- ---------- ---------- ----------KKK????????????????????????????????????? 43???? 435329????????? 8KKK????????????????????????????????????? 48???? 436745????????? 8KKK????????????????????????????????????? 48???? 436753????????? 8KKK????????????????????????????????????? 48???? 436761????????? 8KKK????????????????????????????????????? 48???? 436769????????? 8KKK????????????????????????????????????? 48???? 436777????????? 8KKK????????????????????????????????????? 48???? 436873??????? 128KKK????????????????????????????????????? 40???? 444297??????? 128KKK????????????????????????????????????? 43???? 447241??????? 128KKK????????????????????????????????????? 52???? 449545??????? 128KKK?????????????????????????????????????? 2???? 458249??????? 128?22 rows selected.?SQL> SELECT HEADER_FILE? 2?????? , HEADER_BLOCK? 3?????? , BYTES? 4?????? , BLOCKS? 5?????? , EXTENTS ? 6? FROM DBA_SEGMENTS ? 7? WHERE OWNER='TEST'?AND SEGMENT_NAME='KKK';?HEADER_FILE HEADER_BLOCK????? BYTES???? BLOCKS??? EXTENTS----------- ------------ ---------- ---------- ----------???????? 43?????? 435249??? 7340032??????? 896???????? 22?SQL> 

SQL> alter system dump datafile 43 block 435249;?System altered.?SQL> select user_dump.value?? 2???????? || '/'?? 3???????? || lower(instance.value) ? 4???????? || '_ora_'?? 5???????? || v$process.spid ? 6???????? || nvl2(v$process.traceid, '_'?? 7??????????????????????????????????? || v$process.traceid, null) ? 8???????? || '.trc'"trace file" ? 9? from?? v$parameter user_dump  10???????? cross?join v$parameter instance  11???????? cross?join v$process  12???????? join v$session? 13?????????? on v$process.addr = v$session.paddr  14? where? user_dump.name = 'user_dump_dest'? 15???????? and instance.name = 'instance_name'? 16???????? and v$session.audsid = sys_context('userenv', 'sessionid');? ?trace file--------------------------------------------------------------------/u01/app/oracle/admin/SCM2/udump/scm2_ora_27792.trc

如下所示，塊類型為DATA SEGEMENT HEADER -UNLIMITED ， rdba:( segment header的塊地址為)為 0x0ac6a431 .其實這是第一個塊（不是以block_id大小來看），因為手工段空間管理，這種技術的具體實現方式是通過在段頭（Segment Header）分配自由列表（freelist）來管理Block的使用。簡單一點，你可以把自由列表想象成一個數據結構中的鏈表一樣的數據結構，ORACLE通過一系列算法向自由列表（freelist）中加入或移出Block來實現段管理。

Segment Header是一個Segment的第一個extent的頭塊（第一個塊）。在FLM管理的Segment中，header block始終是segment 的第一個塊。如下所示，在Extent Map中，第一個區間的地址為0x0ac6a432，恰恰跟segment header的塊地址 0x0ac6a431 相差為1，這意味著后面的分配是緊挨著segment header的塊地址。所以在手工段空間管理（Manual Segment Space Management）的表空間，不能以block_id的大小順序來看區間分配順序。也就是說FILE_ID=39 BLOCK_ID=427785的塊并不是第一個區間的第一個塊。這也是我在實驗當中糾結了好久的地方。