[20161108]關於資料檔案的問題.txt
[20161108]關於資料檔案的問題.txt
--昨天看了一些資料檔案點陣圖問題，今天探究資料檔案的其他問題。
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.段空間ASSM或MSSM。
--資料檔案表空間支援段空間自動管理和手動管理。
SCOTT@book> select * from dba_extents where owner=user and segment_name='EMP';
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------ ------------ ------------ --------------- ---------- ---------- ---------- ---------- ---------- ------------
SCOTT EMP TABLE USERS 0 4 144 65536 8 4
SCOTT@book> alter system dump datafile 4 block min 144 block max 151;
System altered.
$ egrep 'type: 0x|buffer tsn' /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_54197.trc
buffer tsn: 4 rdba: 0x01000090 (4/144)
frmt: 0x02 chkval: 0x9220 type: 0x20=FIRST LEVEL BITMAP BLOCK
buffer tsn: 4 rdba: 0x01000091 (4/145)
frmt: 0x02 chkval: 0xd2fc type: 0x21=SECOND LEVEL BITMAP BLOCK
buffer tsn: 4 rdba: 0x01000092 (4/146)
frmt: 0x02 chkval: 0xffc0 type: 0x23=PAGETABLE SEGMENT HEADER
buffer tsn: 4 rdba: 0x01000093 (4/147)
frmt: 0x02 chkval: 0xb843 type: 0x06=trans data
buffer tsn: 4 rdba: 0x01000094 (4/148)
frmt: 0x02 chkval: 0xeca2 type: 0x06=trans data
buffer tsn: 4 rdba: 0x01000095 (4/149)
frmt: 0x02 chkval: 0x6932 type: 0x06=trans data
buffer tsn: 4 rdba: 0x01000096 (4/150)
frmt: 0x02 chkval: 0xc695 type: 0x06=trans data
buffer tsn: 4 rdba: 0x01000097 (4/151)
frmt: 0x02 chkval: 0x332e type: 0x06=trans data
buffer tsn: 4 rdba: 0x01000098 (4/152)
--ASSM存在FIRST LEVEL BITMAP BLOCK,SECOND LEVEL BITMAP BLOCK,PAGETABLE SEGMENT HEADER.如果表很大很大也許還存在THIRD LEVEL BITMAP BLOCK(很少見)。
--在段的分配上還有EXTENT MANAGEMENT LOCAL AUTOALLOCATE 和 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 。
--後者每個段統一大小，2者各有個的優缺點。我一般使用前者EXTENT MANAGEMENT LOCAL AUTOALLOCATE。
--如果有一些大表我會使用單獨的表空間採用EXTENT MANAGEMENT LOCAL UNIFORM SIZE。而且一般我會設定UNIFORM SIZE至少64M。
--這樣當你建立表空間時如果採用SEGMENT SPACE MANAGEMENT auto，UNIFORM SIZE不能設定太小，例子：
CREATE TABLESPACE SUGAR DATAFILE
'/mnt/ramdisk/book/suagr01.dbf' SIZE 40M AUTOEXTEND OFF
LOGGING
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 8K
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT auto
FLASHBACK ON;
*
ERROR at line 1:
ORA-03249: Uniform size for auto segment space managed tablespace should have atleast 5 blocks
--也就是這種模式UNIFORM SIZE 最少5個塊，便於容納FIRST LEVEL BITMAP BLOCK，SECOND LEVEL BITMAP BLOCK，PAGETABLE SEGMENT HEADER。
3.下面做一些測試：
--建立資料檔案最小是多少，昨天我測試建立ASSM的情況，參考連結：http://blog.itpub.net/267265/viewspace-2127936/
--對於MSSM呢？假設UNIFORM SIZE 8K(這個已經是最小UNIFORM SIZE).
--這樣點陣圖區必須1個點陣圖區頭，1個點陣圖區。檔案頭佔1塊，資料塊至少2塊(這種模式使用freelist管理)，這樣最小的資料檔案是40k。
CREATE TABLESPACE SUGAR DATAFILE
'/mnt/ramdisk/book/sugar01.dbf' SIZE 40k AUTOEXTEND OFF
LOGGING
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 8K
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON;
$ ls -l /mnt/ramdisk/book/suagr01.dbf
-rw-r----- 1 oracle oinstall 49152 2016-11-08 09:00:19 /mnt/ramdisk/book/suagr01.dbf
--49152/1024=48K.
SCOTT@book> create table xx tablespace sugar as select * from emp where rownum=1;
Table created.
SCOTT@book> select * from dba_extents where owner=user and segment_name='XX';
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------ ------------ ------------ --------------- --------- ------------ ------------ ------------ ------------ ------------
SCOTT XX TABLE SUGAR 0 6 4 16384 2 6
SCOTT@book> select HEADER_FILE,HEADER_BLOCK from dba_segments where owner=user and segment_name='XX';
HEADER_FILE HEADER_BLOCK
------------ ------------
6 4
SYS@book> alter system checkpoint;
System altered.
SYS@book> alter system dump datafile 6 block min 1 block max 5;
System altered.
$ egrep 'type: 0x|buffer tsn' /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_54353.trc
buffer tsn: 7 rdba: 0x01800002 (6/2)
frmt: 0x02 chkval: 0x9ab0 type: 0x1d=KTFB Bitmapped File Space Header
buffer tsn: 7 rdba: 0x01800003 (6/3)
frmt: 0x02 chkval: 0x4e49 type: 0x1e=KTFB Bitmapped File Space Bitmap
buffer tsn: 7 rdba: 0x01800004 (6/4)
frmt: 0x02 chkval: 0xbca0 type: 0x10=DATA SEGMENT HEADER - UNLIMITED
buffer tsn: 7 rdba: 0x01800005 (6/5)
frmt: 0x02 chkval: 0xd979 type: 0x06=trans data
4.這樣的檔案能擴充套件到多少，點陣圖區僅僅1塊.如果檔案變大，點陣圖在那裡呢？
--繼續做一些測試，後面的測試千萬不要在生產系統進行!!
SYS@book> alter system dump datafile 6 block 3;
System altered.
buffer tsn: 7 rdba: 0x01800003 (6/3)
scn: 0x0000.0029286f seq: 0x01 flg: 0x04 tail: 0x286f1e01
frmt: 0x02 chkval: 0x4e49 type: 0x1e=KTFB Bitmapped File Space Bitmap
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F9C8BD02A00 to 0x00007F9C8BD04A00
7F9C8BD02A00 0000A21E 01800003 0029286F 04010000 [........o().....]
7F9C8BD02A10 00004E49 00000006 00000004 00000000 [IN..............]
7F9C8BD02A20 00000001 0000F7FF 00000000 00000000 [................]
7F9C8BD02A30 00000000 00000000 00000001 00000000 [................]
7F9C8BD02A40 00000000 00000000 00000000 00000000 [................]
Repeat 506 times
7F9C8BD049F0 00000000 00000000 00000000 286F1E01 [..............o(]
File Space Bitmap Block:
BitMap Control:
RelFno: 6, BeginBlock: 4, Flag: 0, First: 1, Free: 63487
0100000000000000 0000000000000000 0000000000000000 0000000000000000
--首先遇到一個奇怪的問題，明明使用2個資料塊，應該標識2個1.而現在僅僅1個。而如果你看錶空間的定義實際上如下：
SYS@book> select dbms_metadata.get_ddl( 'TABLESPACE', 'SUGAR') c100 from dual;
C100
----------------------------------------------------------------------------------------------------
CREATE TABLESPACE "SUGAR" DATAFILE
'/mnt/ramdisk/book/sugar01.dbf' SIZE 40960
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16384 DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT MANUAL
--這樣看實際上最小的UNIFORM SIZE=16K,雖然上面的執行沒有報錯，實際上建立的UNIFORM SIZE=16K。
--如果把點陣圖區全部變成1，要建立多大的檔案：
--63487+1 =63488
--63488*16384=1040187392
--1040187392/1024/1024=992M
--1040187392/1024+16=1015824K
--63488*2=126976
SYS@book> ALTER DATABASE DATAFILE '/mnt/ramdisk/book/sugar01.dbf' AUTOEXTEND ON NEXT 16K MAXSIZE 1015824K;
Database altered.
SYS@book> @ &r/desc_proc sys DBMS_SPACE_ADMIN TABLESPACE_REBUILD_BITMAPS
INPUT OWNER PACKAGE_NAME OBJECT_NAME
sample : @desc_proc sys dbms_stats gather_%_stats
OWNER PACKAGE_NAME OBJECT_NAME SEQUENCE ARGUMENT_NAME DATA_TYPE IN_OUT DATA_TYPE DEFAULTED
---------- -------------------- ------------------------------ ---------- -------------------- -------------------- --------- -------------------- ----------
SYS DBMS_SPACE_ADMIN TABLESPACE_REBUILD_BITMAPS 3 BITMAP_BLOCK BINARY_INTEGER IN BINARY_INTEGER Y
2 BITMAP_RELATIVE_FILE BINARY_INTEGER IN BINARY_INTEGER Y
1 TABLESPACE_NAME VARCHAR2 IN VARCHAR2 N
--注意千萬不要在生產系統執行如下命令!!
-- EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_REBUILD_BITMAPS('SUGAR');
SYS@book> ALTER DATABASE DATAFILE '/mnt/ramdisk/book/sugar01.dbf' RESIZE 1015824K;
Database altered.
SYS@book> EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_REBUILD_BITMAPS('SUGAR');
PL/SQL procedure successfully completed.
SYS@book> alter system checkpoint;
System altered.
SYS@book> alter system dump datafile 6 block 3;
System altered.
--檢查轉儲檔案。
Block dump from disk:
buffer tsn: 7 rdba: 0x01800003 (6/3)
scn: 0x0000.00292e85 seq: 0x01 flg: 0x04 tail: 0x2e851e01
frmt: 0x02 chkval: 0x41b6 type: 0x1e=KTFB Bitmapped File Space Bitmap
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F9C8BD02A00 to 0x00007F9C8BD04A00
7F9C8BD02A00 0000A21E 01800003 00292E85 04010000 [..........).....]
7F9C8BD02A10 000041B6 00000006 00000004 00000000 [.A..............]
7F9C8BD02A20 0000F800 00000000 00000000 00000000 [................]
7F9C8BD02A30 00000000 00000000 FFFFFFFF FFFFFFFF [................]
7F9C8BD02A40 FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF [................]
Repeat 494 times
7F9C8BD04930 FFFFFFFF FFFFFFFF 00000000 00000000 [................]
7F9C8BD04940 00000000 00000000 00000000 00000000 [................]
Repeat 10 times
7F9C8BD049F0 00000000 00000000 00000000 2E851E01 [................]
File Space Bitmap Block:
BitMap Control:
RelFno: 6, BeginBlock: 4, Flag: 0, First: 63488, Free: 0
FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF
...
FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF
FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF
FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF
End dump data blocks tsn: 7 file#: 6 minblk 3 maxblk 3
--你可以發現執行DBMS_SPACE_ADMIN.TABLESPACE_REBUILD_BITMAPS('SUGAR')後點陣圖區全部變成F，也就是全部佔用。
SCOTT@book> alter system dump datafile 6 block 2;
System altered.
Block dump from disk:
buffer tsn: 7 rdba: 0x01800002 (6/2)
scn: 0x0000.00292e82 seq: 0x01 flg: 0x04 tail: 0x2e821d01
frmt: 0x02 chkval: 0x6aab type: 0x1d=KTFB Bitmapped File Space Header
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F1F94E18200 to 0x00007F1F94E1A200
7F1F94E18200 0000A21D 01800002 00292E82 04010000 [..........).....]
7F1F94E18210 00006AAB 00000006 00000002 0001F002 [.j..............]
7F1F94E18220 00000019 00000002 0001F002 00000002 [................]
7F1F94E18230 0001F001 00000000 00000000 000E2008 [............. ..]
7F1F94E18240 00000000 00000000 00000000 00000000 [................]
7F1F94E18250 00000004 00000002 00000000 00000000 [................]
7F1F94E18260 00000000 00000000 00000000 00000000 [................]
Repeat 504 times
7F1F94E1A1F0 00000000 00000000 00000000 2E821D01 [................]
File Space Header Block:
Header Control:
RelFno: 6, Unit: 2, Size: 126978, Flag: 25
AutoExtend: YES, Increment: 2, MaxSize: 126978
Initial Area: 2, Tail: 126977, First: 0, Free: 0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Deallocation scn: 925704.0
Header Opcode:
Save: No Pending Op
End dump data blocks tsn: 7 file#: 6 minblk 2 maxblk 2
--注意看～部分，Tail: 126977.
--我資料檔案resize到1015824K，1015824/8=126978,這樣看看最後一塊的情況。
SCOTT@book> alter system checkpoint;
System altered.
SCOTT@book> alter system dump datafile 6 block 126978;
System altered.
Start dump data blocks tsn: 7 file#:6 minblk 126978 maxblk 126978
Block dump from cache:
Dump of buffer cache at level 4 for tsn=7 rdba=25292802
Block dump from disk:
buffer tsn: 7 rdba: 0x0181f002 (6/126978)
scn: 0x0000.00000000 seq: 0x01 flg: 0x05 tail: 0x00000001
frmt: 0x02 chkval: 0x5683 type: 0x00=unknown
Hex dump of block: st=0, typ_found=0
Dump of memory from 0x00007F1F94E18200 to 0x00007F1F94E1A200
7F1F94E18200 0000A200 0181F002 00000000 05010000 [................]
7F1F94E18210 00005683 00000000 00000000 00000000 [.V..............]
7F1F94E18220 00000000 00000000 00000000 00000000 [................]
Repeat 508 times
7F1F94E1A1F0 00000000 00000000 00000000 00000001 [................]
Dump of memory from 0x00007F1F94E18214 to 0x00007F1F94E1A1FC
7F1F94E18210 00000000 00000000 00000000 [............]
7F1F94E18220 00000000 00000000 00000000 00000000 [................]
Repeat 508 times
7F1F94E1A1F0 00000000 00000000 00000000 [............]
End dump data blocks tsn: 7 file#: 6 minblk 126978 maxblk 126978
--奇怪型別居然為unknown。
SCOTT@book> create table yy tablespace sugar as select * from dept where rownum=1;
Table created.
SCOTT@book> column PARTITION_NAME noprint
SCOTT@book> select * from dba_extents where owner=user and file_id=6;
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------ -------------------- ------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------------
SCOTT XX TABLE SUGAR 0 6 4 16384 2 6
SCOTT YY TABLE SUGAR 0 6 6 16384 2 6
--居然可以使用標誌1的空間!!
SYS@book> select * from DBA_FREE_SPACE where tablespace_name='SUGAR';
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------------------------------ ---------- ---------- ---------- ---------- ------------
SUGAR 6 8 1040138240 126970 6
--可以發現還有126970塊沒有使用，重啟資料庫也一樣。
SCOTT@book> create table zz tablespace sugar as select rownum id from dual connect by level<=2;
Table created.
SCOTT@book> ALTER TABLE zz MINIMIZE RECORDS_PER_BLOCK ;
Table altered.
SCOTT@book> select * from DBA_FREE_SPACE where tablespace_name='SUGAR';
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------------------------------ ---------- ---------- ---------- ---------- ------------
SUGAR 6 10 1040121856 126968 6
-- 126968*2=253936.
SCOTT@book> insert into zz select rownum+2 id from dual connect by level<=253936 ;
insert into zz select rownum+2 id from dual connect by level<=253936
*
ERROR at line 1:
ORA-01653: unable to extend table SCOTT.ZZ by 2 in tablespace SUGAR
SCOTT@book> insert into zz select rownum+2 id from dual connect by level<=253934;
insert into zz select rownum+2 id from dual connect by level<=253934
*
ERROR at line 1:
ORA-01653: unable to extend table SCOTT.ZZ by 2 in tablespace SUGAR
--不行,重來。
drop table xx purge ;
drop table yy purge ;
drop table zz purge ;
create table zz tablespace sugar as select rownum id from dual connect by level<=2;
ALTER TABLE zz MINIMIZE RECORDS_PER_BLOCK ;
SCOTT@book> select * from DBA_FREE_SPACE where tablespace_name='SUGAR';
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------------------------------ ---------- ---------- ---------- ---------- ------------
SUGAR 6 6 1040154624 126972 6
SCOTT@book> insert into zz select rownum+2 id from dual connect by level<=253823;
insert into zz select rownum+2 id from dual connect by level<=253823
*
ERROR at line 1:
ORA-01653: unable to extend table SCOTT.ZZ by 2 in tablespace SUGAR
SCOTT@book> truncate table zz;
Table truncated.
SCOTT@book> insert into zz select rownum+2 id from dual connect by level<=253822;
253822 rows created.
SCOTT@book> commit ;
Commit complete.
--也就是將有一些塊存在其他用途。
SCOTT@book> alter system checkpoint;
System altered.
SCOTT@book> alter system dump datafile 6 block 4 ;
System altered.
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 63487 #blocks: 126911
last map 0x0181e994 #maps: 62 offset: 4128
Highwater:: 0x0181f002 ext#: 63486 blk#: 2 ext size: 2
#blocks in seg. hdr's freelists: 1
#blocks below: 126911
mapblk 0x0181e994 offset: 822
Unlocked
Map Header:: next 0x018003f6 #extents: 505 obj#: 88929 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
--噢，忽略一個問題，如果DATA SEGMENT HEADER - UNLIMITED寫滿了，要透過增加塊來建立map。
SCOTT@book> @ &r/dfb16 0x018003f6
RFILE# BLOCK# TEXT
---------- ---------- ------------------------------------------------------------
6 1014 alter system dump datafile 6 block 1014 ;
SCOTT@book> alter system dump datafile 6 block 1014 ;
System altered.
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 63487 #blocks: 126911
last map 0x0181e994 #maps: 62 offset: 4128
Highwater:: 0x0181f002 ext#: 63486 blk#: 2 ext size: 2
#blocks in seg. hdr's freelists: 1
#blocks below: 126911
mapblk 0x0181e994 offset: 822
Unlocked
Map Header:: next 0x018003f6 #extents: 505 obj#: 88929 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x01800005 length: 1
0x01800006 length: 2
0x01800008 length: 2
....
SCOTT@book> alter system dump datafile 6 block 1014 ;
System altered.
*** 2016-11-08 12:06:15.471
Start dump data blocks tsn: 7 file#:6 minblk 1014 maxblk 1014
Block dump from cache:
Dump of buffer cache at level 4 for tsn=7 rdba=25166838
Block dump from disk:
buffer tsn: 7 rdba: 0x018003f6 (6/1014)
scn: 0x0000.005d06f6 seq: 0x01 flg: 0x04 tail: 0x06f61201
frmt: 0x02 chkval: 0xf6ba type: 0x12=EXTENT MAP BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F2D432E8200 to 0x00007F2D432EA200
7F2D432E8200 0000A212 018003F6 005D06F6 04010000 [..........].....]
7F2D432E8210 0000F6BA 000003FB 01800BEC 00015B61 [............a[..]
7F2D432E8220 10000000 018003F7 00000001 018003F8 [................]
7F2D432E8230 00000002 018003FA 00000002 018003FC [................]
...
7F2D432EA1F0 00000002 01800BEA 00000002 06F61201 [................]
EMB Dump:
Map Header:: next 0x01800bec #extents: 1019 obj#: 88929 flag: 0x10000000
Extent Map
-----------------------------------------------------------------
-- dba = 6,4 #extents: 505 ,而dba = 6 ,1014 #extents: 1019
-- 平均以1000個extents計算。
126972/1000/2=63.486 大約64.
126972 - 64=126908
126908*2=253816
--而實際能插入253822。基本接近了。
轉儲alter system dump datafile 6 block 2;顯示：
Initial Area: 2, Tail: 126977, First: 0, Free: 0
SCOTT@book> select * from (select * from dba_extents where file_id=6 order by block_id desc) where rownum=1;
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------ ------------ ------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------------
SCOTT ZZ TABLE SUGAR 63486 6 126976 16384 2 6
--這樣126978 僅僅剩下一塊不夠擴充套件.忽略轉儲資訊,少算了3塊：
RelFno: 6, BeginBlock: 4, Flag: 0, First: 1, Free: 63487
--63488*2=126976
--126976+4-1=126979
--126979*8192/1024=1015832.00000000000000000000
--1015824+8*3=1015848
SCOTT@book> ALTER DATABASE DATAFILE '/mnt/ramdisk/book/sugar01.dbf' RESIZE 1015848K;
Database altered.
--正常可以插入6條記錄。
SCOTT@book> insert into zz select 1e5+rownum id from dual connect by level<=2;
2 rows created.
SCOTT@book> insert into zz select 1e5+rownum id from dual connect by level<=2;
2 rows created.
SCOTT@book> insert into zz select 1e5+rownum id from dual connect by level<=2;
insert into zz select 1e5+rownum id from dual connect by level<=2
*
ERROR at line 1:
ORA-01653: unable to extend table SCOTT.ZZ by 2 in tablespace SUGAR
--可以發現僅僅插入4條。
SCOTT@book> insert into zz select 1e5+rownum id from dual connect by level<=4;
4 rows created.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> alter system dump datafile 6 block min 126978 block max 126981;
System altered.
$ egrep 'type: 0x|buffer tsn' /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_55794.trc
buffer tsn: 7 rdba: 0x0181f002 (6/126978)
frmt: 0x02 chkval: 0xb7e7 type: 0x06=trans data
buffer tsn: 7 rdba: 0x0181f003 (6/126979)
frmt: 0x02 chkval: 0x1d30 type: 0x06=trans data
buffer tsn: 7 rdba: 0x0181f004 (6/126980)
frmt: 0x02 chkval: 0x5685 type: 0x00=unknown
buffer tsn: 7 rdba: 0x0181f005 (6/126981)
frmt: 0x02 chkval: 0x5684 type: 0x00=unknown
SCOTT@book> alter system dump datafile 6 block 2;
System altered.
File Space Header Block:
Header Control:
RelFno: 6, Unit: 2, Size: 126981, Flag: 9
AutoExtend: YES, Increment: 2, MaxSize: 126978
Initial Area: 2, Tail: 126979, First: 63488, Free: 0
Deallocation scn: 6095082.0
Header Opcode:
Save: No Pending Op
End dump data blocks tsn: 7 file#: 6 minblk 2 maxblk 2
--可以發現最後2塊無法使用。再增加1個extent。
SCOTT@book> ALTER DATABASE DATAFILE '/mnt/ramdisk/book/sugar01.dbf' RESIZE 1015864K;
Database altered.
SCOTT@book> insert into zz select 1e5+rownum id from dual connect by level<=2;
2 rows created.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> alter system checkpoint;
System altered.
SCOTT@book> alter system dump datafile 6 block min 126980 block max 126983;
System altered.
$ egrep 'type: 0x|buffer tsn' /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_55897.trc
buffer tsn: 7 rdba: 0x0181f004 (6/126980)
frmt: 0x02 chkval: 0x20b9 type: 0x06=trans data
buffer tsn: 7 rdba: 0x0181f005 (6/126981)
frmt: 0x02 chkval: 0xf034 type: 0x06=trans data
buffer tsn: 7 rdba: 0x0181f006 (6/126982)
frmt: 0x02 chkval: 0x4e3b type: 0x1e=KTFB Bitmapped File Space Bitmap
buffer tsn: 7 rdba: 0x0181f007 (6/126983)
frmt: 0x02 chkval: 0x5686 type: 0x00=unknown
--你可以發現dba= 6,126982 對於的是KTFB Bitmapped File Space Bitmap。
SCOTT@book> alter system dump datafile 6 block 2;
System altered.
File Space Header Block:
Header Control:
RelFno: 6, Unit: 2, Size: 126983, Flag: 9
AutoExtend: YES, Increment: 2, MaxSize: 126978
Initial Area: 2, Tail: 126981, First: 63489, Free: 0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Deallocation scn: 6095082.0
Header Opcode:
Save: No Pending Op
End dump data blocks tsn: 7 file#: 6 minblk 2 maxblk 2
--你可以發現現Tail: 126981. 後面的塊是點陣圖區。
5.繼續測試：
SCOTT@book> insert into zz select 1e5+rownum id from dual connect by level<=2;
2 rows created.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> ALTER DATABASE DATAFILE '/mnt/ramdisk/book/sugar01.dbf' AUTOEXTEND ON NEXT 16K MAXSIZE UNLIMITED;
Database altered.
SCOTT@book> insert into zz select 1e5+rownum id from dual connect by level<=2;
2 rows created.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> alter system checkpoint;
System altered.
SCOTT@book> alter system dump datafile 6 block 2;
System altered.
File Space Header Block:
Header Control:
RelFno: 6, Unit: 2, Size: 133335, Flag: 9
AutoExtend: YES, Increment: 2, MaxSize: 4194302
Initial Area: 2, Tail: 133333, First: 63490, Free: 3175
Deallocation scn: 6095082.0
Header Opcode:
Save: No Pending Op
End dump data blocks tsn: 7 file#: 6 minblk 2 maxblk 2
--可以發現tail:133333.
SCOTT@book> alter system dump datafile 6 block 126982;
System altered.
SCOTT@book> alter system dump datafile 6 block 133334;
System altered.
$ egrep 'type: 0x|buffer tsn' /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_55897.trc
buffer tsn: 7 rdba: 0x0181f006 (6/126982)
frmt: 0x02 chkval: 0xce13 type: 0x06=trans data
buffer tsn: 7 rdba: 0x018208d6 (6/133334)
frmt: 0x02 chkval: 0xb6e8 type: 0x1e=KTFB Bitmapped File Space Bitmap
--你可以發現點陣圖區變成了dba=6,133334,而dba=6,126982,變成了存放資料。
6.總結：
1.測試還是亂。
2.在8k資料塊下，最小的資料檔案是40k，SEGMENT SPACE MANAGEMENT MANUAL，並且最小EXTENT MANAGEMENT LOCAL UNIFORM SIZE＝16K。
3.這樣在點陣圖區使用完後，隨著資料檔案加大，點陣圖頭塊記錄的tail變大，後面總有1點剩餘作為點陣圖區。
4.EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_REBUILD_BITMAPS('SUGAR'); 會把點陣圖區置為1，但是視乎對應用沒有什麼影響。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2128006/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 關於SQLServer的tempdb的資料檔案暴增問題（1）SQLServer
- 關於資料表結構sql檔案匯入mysql資料庫的問題？MySql資料庫
- 關於SAX解析xml檔案的問題XML
- 關於解壓zip檔案的問題
- 關於資料共享的問題
- 關於讀取資料庫配置資原始檔問題資料庫
- oracle 關於-資料檔案Oracle
- 關於oracle檔案許可權的問題Oracle
- 關於檔案上傳的問題smartUpload
- [20171228]關於資料塊轉儲的問題.txt
- [20121108]關於克隆資料庫的問題.txt資料庫
- [20150527]bbed解決資料檔案大小問題.txt
- 關於配置檔案中的預設值的問題
- 關於海量資料的獲取問題
- [20121115]關於oracle資料檔案的第1塊.txtOracle
- java 讀取.txt檔案時，注意的問題Java
- 請問，關於資料庫連線的問題。資料庫
- 關於檔案上傳下載的編碼問題
- 關於FileDownloader檔案長度校驗的問題
- 關於 Xcode 9 拖入檔案未生效的問題XCode
- 請教高手關於解析xml檔案的問題 急～～XML
- [20180211]關於oracle執行檔案許可權問題.txtOracle
- 關於資料庫檔案最大數資料庫
- [20161107]關於資料檔案點陣圖區.txt
- 有關*.properties檔案的問題
- 關於收縮資料檔案的嘗試
- [20190930]關於資料結構設計問題.txt資料結構
- 求救：關於讀取excel資料的問題Excel
- 關於資料庫和jdbc的問題，指教資料庫JDBC
- 關於資料倉儲和OLAP的問題！
- 關於資料庫緩衝池的問題資料庫
- 關於倒資料的速度記錄問題
- 請教一個問題，關於上傳檔案和圖片的問題
- 關於建立Android工程R檔案丟失的問題Android
- C++雜談之關於檔案操作的若干問題C++
- 關於redo log 檔案中記錄的內容問題 ？
- 關於websphere讀取war配置檔案出錯的問題Web
- dfm檔案資料丟失問題