[20150116]系統管理表空間的疑問3.txt
--前幾天做了系統管理表空間的探究以及oracle的存儲結構(主要集中在10g,11g).又犯了一個經驗錯誤。
--特此更正,原始連結如下:
[20150113]關于oracle的存儲結構.txt
<a href="http://blog.itpub.net/267265/viewspace-1400603/">http://blog.itpub.net/267265/viewspace-1400603/</a>
[20150113]系統管理表空間的疑問2.txt
<a href="http://blog.itpub.net/267265/viewspace-1399890/">http://blog.itpub.net/267265/viewspace-1399890/</a>
[20150112]系統管理表空間的疑問.txt
<a href="http://blog.itpub.net/267265/viewspace-1399275/">http://blog.itpub.net/267265/viewspace-1399275/</a>
--實際上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
------------------------------ -------------- ----------------------------------------------------------------
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;
8