[20161123]oracle資料塊型別.txt

[20161123]oracle資料塊型別.txt

--oracle 資料塊有許多型別，自己平時很少關注與記憶，自己做一個歸納總結：

1.環境:
SCOTT@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.轉儲測試:
SCOTT@book> alter system dump datafile 4 block min 2 block max 131;
System altered.

--//不能使用上面的方式轉儲block 1. 這塊是檔案頭。
--11G下如果你建立的資料檔案不是很小，資料檔案第0塊是OS，第1塊是檔案頭塊，第2塊點陣圖區檔案頭，第3-127塊是點陣圖區。

$ egrep 'type: 0x|buffer tsn' /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_19737.trc
buffer tsn: 4 rdba: 0x01000002 (4/2)
frmt: 0x02 chkval: 0x3389 type: 0x1d=KTFB Bitmapped File Space Header
buffer tsn: 4 rdba: 0x01000003 (4/3)
frmt: 0x02 chkval: 0xb03c type: 0x1e=KTFB Bitmapped File Space Bitmap
buffer tsn: 4 rdba: 0x01000004 (4/4)
....
buffer tsn: 4 rdba: 0x0100007f (4/127)
frmt: 0x02 chkval: 0x4224 type: 0x1e=KTFB Bitmapped File Space Bitmap
buffer tsn: 4 rdba: 0x01000080 (4/128)
frmt: 0x02 chkval: 0x9226 type: 0x20=FIRST LEVEL BITMAP BLOCK
buffer tsn: 4 rdba: 0x01000081 (4/129)
frmt: 0x02 chkval: 0xd2ea type: 0x21=SECOND LEVEL BITMAP BLOCK
buffer tsn: 4 rdba: 0x01000082 (4/130)
frmt: 0x02 chkval: 0xffc6 type: 0x23=PAGETABLE SEGMENT HEADER
buffer tsn: 4 rdba: 0x01000083 (4/131)
frmt: 0x02 chkval: 0x705d type: 0x06=trans data

--從上面的輸出可以知道

KTFB Bitmapped File Space Header 0x1d
KTFB Bitmapped File Space Bitmap 0x1e
FIRST LEVEL BITMAP BLOCK         0x20
SECOND LEVEL BITMAP BLOCK        0x21
PAGETABLE SEGMENT HEADER         0x23
trans data                       0x06

--不知道THIRD LEVEL BITMAP BLOCK 是否是 0x22.很少見，不做測試了。

frmt: 0x02 chkval: 0xa780 type: 0x00=unknown
buffer tsn: 7 rdba: 0x00000081 (0/129)

3.看看檔案頭：
BBED> dump /v dba 4,1 count 64 offset 0
File: /mnt/ramdisk/book/users01.dbf (4)
Block: 1                                 Offsets:    0 to   63                            Dba:0x01000001
-----------------------------------------------------------------------------------------------------------
0ba20000 01000001 00000000 00000104 8ecf0000 00000000 0004200b 6e21b74f l .......................... .n!.O
424f4f4b 00000000 db9d0000 00190000 00200000 04000300 00000000 00000000 l BOOK............. ..............
<32 bytes per line>

BBED> p kcvfh.kcvfhbfh.type_kcbh dba 4,1
ub1 type_kcbh                               @0        0x0b

--可以確定檔案頭 file head block=0x0b

4.資料檔案4對於表空間是users，採用assm(SEGMENT SPACE MANAGEMENT AUTO).
--看看mssm的情況。

CREATE TABLESPACE sugar DATAFILE
  '/mnt/ramdisk/book/sugar01.dbf' SIZE 40M AUTOEXTEND ON NEXT 16K MAXSIZE UNLIMITED
LOGGING
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON;

SCOTT@book> create table t4 tablespace sugar as select rownum id ,lpad('A',32,'A') name from dual connect by level<=1e5;
Table created.

SCOTT@book> alter system dump datafile 6 block min 2 block max 131;
System altered.

$ egrep 'type: 0x|buffer tsn' /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_19992.trc
...
buffer tsn: 7 rdba: 0x01800080 (6/128)
frmt: 0x02 chkval: 0xb8fb type: 0x10=DATA SEGMENT HEADER - UNLIMITED

DATA SEGMENT HEADER - UNLIMITED 0x10

--如果DATA SEGMENT HEADER - UNLIMITED寫滿。參考連線http://blog.itpub.net/267265/viewspace-2128006/
--0x12=EXTENT MAP BLOCK

EXTENT MAP BLOCK  = 0x12

5.繼續測試：
SCOTT@book> drop table t4 purge ;
Table dropped.

--mssm 支援freelist以及freegroup引數，僅僅對mssm有效。freegroup必須在建立時指定，看看這些塊的型別。

SCOTT@book> create table t4 tablespace sugar storage (freelists 10 freelist groups 5) as select rownum id ,lpad('A',32,'A') name from dual connect by level<=1e5;
Table created.

SCOTT@book> alter system dump datafile 6 block min 2 block max 140;
System altered.

frmt: 0x02 chkval: 0xb9f2 type: 0x11=DATA SEGMENT HEADER WITH FREE LIST BLKS - UNLIMITED
buffer tsn: 7 rdba: 0x01800081 (6/129)
frmt: 0x02 chkval: 0xed61 type: 0x16=DATA SEGMENT FREE LIST BLOCK WITH FREE BLOCK COUNT
buffer tsn: 7 rdba: 0x01800082 (6/130)
frmt: 0x02 chkval: 0xed62 type: 0x16=DATA SEGMENT FREE LIST BLOCK WITH FREE BLOCK COUNT
buffer tsn: 7 rdba: 0x01800083 (6/131)
frmt: 0x02 chkval: 0xed63 type: 0x16=DATA SEGMENT FREE LIST BLOCK WITH FREE BLOCK COUNT
buffer tsn: 7 rdba: 0x01800084 (6/132)
frmt: 0x02 chkval: 0xed64 type: 0x16=DATA SEGMENT FREE LIST BLOCK WITH FREE BLOCK COUNT
buffer tsn: 7 rdba: 0x01800085 (6/133)
frmt: 0x02 chkval: 0xed65 type: 0x16=DATA SEGMENT FREE LIST BLOCK WITH FREE BLOCK COUNT
buffer tsn: 7 rdba: 0x01800086 (6/134)

DATA SEGMENT FREE LIST BLOCK WITH FREE BLOCK COUNT  =0x16
DATA SEGMENT HEADER WITH FREE LIST BLKS - UNLIMITED =0x11

--估計使用這個會越來越少。補充1點：
--如果塊格式化未使用 0x00  =unknown，當然也可那個有問題。

6.總結：
--畫一個表格總結如下：
===========================================================
KTFB Bitmapped File Space Header                      0x1d
KTFB Bitmapped File Space Bitmap                      0x1e
FIRST LEVEL BITMAP BLOCK                              0x20
SECOND LEVEL BITMAP BLOCK                             0x21
PAGETABLE SEGMENT HEADER                              0x23
trans data                                            0x06
DATA SEGMENT HEADER - UNLIMITED                       0x10
EXTENT MAP BLOCK                                      0x12
DATA SEGMENT HEADER WITH FREE LIST BLKS - UNLIMITED   0x11
DATA SEGMENT FREE LIST BLOCK WITH FREE BLOCK COUNT    0x16
file head block                                       0x0b

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

--//一定還有一些遺漏，以後有機會再補充吧。