[20150116]系统管理表空间的疑问3.txt
--前几天做了系统管理表空间的探究以及oracle的存储结构(主要集中在10g，11g).又犯了一个经验错误。
--特此更正，原始链接如下：
[20150113]关于oracle的存储结构.txt
http://blog.itpub.net/267265/viewspace-1400603/
[20150113]系统管理表空间的疑问2.txt
http://blog.itpub.net/267265/viewspace-1399890/
[20150112]系统管理表空间的疑问.txt
http://blog.itpub.net/267265/viewspace-1399275/
--实际上10g上位图区块是3－8块，2块是位图块头。
--而11G文件保留128块，位图区块多数情况下只要建立的数据文件不是太小(>=1080K),位图区块是3－127，2块是位图块头。
--而11g下建立文件
1.测试环境：
--我的测试环境:数据块大小8K。
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
CREATE TABLESPACE TOOLSX DATAFILE
'/u01/app/oracle11g/oradata/test/tools01x.dbf' SIZE 1M AUTOEXTEND OFF
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
2.测试：
SCOTT@test> create table emp2 tablespace toolsx as select * from emp;
Table created.
SCOTT@test> column PARTITION_NAME noprint
SCOTT@test> select * from dba_extents where owner=user and segment_name='EMP2';
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------ ------------- ------------- -------------------------- ---------- ---------- ---------- ---------- ------------
SCOTT EMP2 TABLE TOOLSX 0 12 8 65536 8 12
alter system dump datafile 12 block min 2 block max 8;
$ egrep 'type: 0x|buffer tsn' test_ora_2245_127_0_0_1.trc
buffer tsn: 1065 rdba: 0x03000002 (12/2)
frmt: 0x02 chkval: 0x2500 type: 0x1d=KTFB Bitmapped File Space Header
buffer tsn: 1065 rdba: 0x03000003 (12/3)
frmt: 0x02 chkval: 0x8608 type: 0x1e=KTFB Bitmapped File Space Bitmap
buffer tsn: 1065 rdba: 0x03000004 (12/4)
frmt: 0x02 chkval: 0x49f7 type: 0x1e=KTFB Bitmapped File Space Bitmap
buffer tsn: 1065 rdba: 0x03000005 (12/5)
frmt: 0x02 chkval: 0x09fe type: 0x1e=KTFB Bitmapped File Space Bitmap
buffer tsn: 1065 rdba: 0x03000006 (12/6)
frmt: 0x02 chkval: 0xc9e5 type: 0x1e=KTFB Bitmapped File Space Bitmap
buffer tsn: 1065 rdba: 0x03000007 (12/7)
frmt: 0x02 chkval: 0x89ec type: 0x1e=KTFB Bitmapped File Space Bitmap
buffer tsn: 1065 rdba: 0x03000008 (12/8)
frmt: 0x02 chkval: 0xc84e type: 0x20=FIRST LEVEL BITMAP BLOCK
--很明显block_id=8,也就是前面0-7块保留。0=>os块头，1=>文件头，2=>KTFB Bitmapped File Space Header,
--3-7块=>KTFB Bitmapped File Space Bitmap。
--8 块=> FIRST LEVEL BITMAP BLOCK. 属于emp2.
2.在来看看11G的情况：
SYS@icare> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx 10.2.0.4.0 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
SYS@icare> alter system dump datafile 54 block min 2 block max 8;
System altered.
# egrep 'type: 0x|buffer tsn' /u01/app/oracle/admin/icare/udump/icare_ora_30183.trc
buffer tsn: 18 rdba: 0x0d800002 (54/2)
frmt: 0x02 chkval: 0x76a6 type: 0x1d=KTFB Bitmapped File Space Header
buffer tsn: 18 rdba: 0x0d800003 (54/3)
frmt: 0x02 chkval: 0xe53d type: 0x1e=KTFB Bitmapped File Space Bitmap
buffer tsn: 18 rdba: 0x0d800004 (54/4)
frmt: 0x02 chkval: 0x2097 type: 0x1e=KTFB Bitmapped File Space Bitmap
buffer tsn: 18 rdba: 0x0d800005 (54/5)
frmt: 0x02 chkval: 0x7cfd type: 0x1e=KTFB Bitmapped File Space Bitmap
buffer tsn: 18 rdba: 0x0d800006 (54/6)
frmt: 0x02 chkval: 0xb866 type: 0x1e=KTFB Bitmapped File Space Bitmap
buffer tsn: 18 rdba: 0x0d800007 (54/7)
frmt: 0x02 chkval: 0xf725 type: 0x1e=KTFB Bitmapped File Space Bitmap
buffer tsn: 18 rdba: 0x0d800008 (54/8)
frmt: 0x02 chkval: 0x4d8e type: 0x1e=KTFB Bitmapped File Space Bitmap
--很明显block_id=8,也是位图块,也就是前面0-8块保留。0=>os块头，1=>文件头，2=>KTFB Bitmapped File Space Header,
--3-8块=>KTFB Bitmapped File Space Bitmap。
--这样10g与11g还是存在一些细小的差别，也许这样在11G下更好的对齐。
--从这里也可以证明：
--10g
SYS@icare> select min(block_id) from dba_extents where file_id=54;
MIN(BLOCK_ID)
-------------
9
--11g：
SCOTT@test> select min(block_id) from dba_extents where file_id=12;
MIN(BLOCK_ID)
-------------
8
