[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

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

--//一定还有一些遗漏,以后有机会再补充吧。

上一篇:云·企业官网定制——网站建设篇之——阿里云心选商城


下一篇:得到oracle分页SQL语句的函数-三层嵌套sql