[20171206]點陣圖區一定在資料檔案開頭嗎.txt

--//如果問你oracle資料檔案的點陣圖區位於資料檔案開頭部分嗎?我想大家的回答一定,實際上在10g下未必,因為10g建立的資料檔案.
--//在資料區前面僅僅8塊,第1塊作為檔案頭,第2塊作為點陣圖區頭,第3-8塊(共6塊)作為點陣圖區,一般1個點陣圖區塊能容納

--//(494+2)*32*4= 63488區,1個區=64K(對於SEGMENT SPACE MANAGEMENT AUTO).
--//這樣1個點陣圖塊可以容納63488*64*1024=4160749568位元組,相當於4160749568/1024/1024/1024 = 3.875G
--//前面6個點陣圖區塊,僅僅容納 3.875*6 = 23.25G.
--//參考我以前測試:http://blog.itpub.net/267265/viewspace-1399275/

--//這樣32G的資料檔案在10g下,前面的點陣圖區是不夠的,我的測試使用資料檔案的尾部的區域來再建立點陣圖區.
--//從某種意義講在10g如果你建立的資料檔案很大(大於24G),最佳的方法設定固定大小,避免尾部的點陣圖區隨檔案變大而移動.

--//而11g下一般情況下資料檔案開頭1M不作為資料區,這樣有足夠的點陣圖區.
--//實際上在11g下一樣可以演示這樣的情況,透過例子來說明:

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

CREATE TABLESPACE T01 DATAFILE
'/mnt/ramdisk/book/T01.dbf' SIZE 40 K AUTOEXTEND OFF
LOGGING
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 8K
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON;

--//這樣這個資料檔案僅僅1塊點陣圖區.這樣有63488區,1個區=16k,這樣相當於63488*8192*2 = 1040187392位元組
--//注:實際上根本不能建立UNIFORM SIZE 8K的資料檔案,最少2塊.這樣1個區=16K.測試計算錯誤..
--//這樣1個點陣圖塊最多允許 1040187392/1024/1024 = 992M.

2.轉儲分析:
SCOTT@book> alter system dump datafile 7 block min 2 block max 3;
System altered.

--//檢查轉儲檔案:
Start dump data blocks tsn: 9 file#:7 minblk 2 maxblk 3
Block dump from cache:
Dump of buffer cache at level 4 for tsn=9 rdba=29360130
BH (0x677f6fd0) file#: 7 rdba: 0x01c00002 (7/2) class: 13 ba: 0x67764000
set: 71 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 28,19
dbwrid: 1 obj: -1 objn: 24 tsn: 9 afn: 7 hint: f
hash: [0x84b95f68,0x84b95f68] lru: [0x677f6d18,0x843d4cc0]
lru-flags: hot_buffer
ckptq: [NULL] fileq: [NULL] objq: [0x7c18a110,0x7c18a110] objaq: [0x7c18a100,0x7c18a100]
st: XCURRENT md: NULL fpin: 'ktfbwh00: ktfbhfmt' tch: 1
flags: foreground_waiting block_written_once redo_since_read
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1]
Block dump from disk:
buffer tsn: 9 rdba: 0x01c00002 (7/2)
scn: 0x0003.177527e5 seq: 0x01 flg: 0x04 tail: 0x27e51d01
frmt: 0x02 chkval: 0x8da8 type: 0x1d=KTFB Bitmapped File Space Header
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F1EBA962200 to 0x00007F1EBA964200
7F1EBA962200 0000A21D 01C00002 177527E5 04010003 [.........'u.....]
7F1EBA962210 00008DA8 00000007 00000002 00000005 [................]
7F1EBA962220 00000001 00000000 00000000 00000002 [................]
7F1EBA962230 00000005 00000000 00000001 000E2008 [............. ..]
7F1EBA962240 00000000 00000000 00000000 00000000 [................]
Repeat 506 times
7F1EBA9641F0 00000000 00000000 00000000 27E51D01 [...............']
File Space Header Block:
Header Control:
RelFno: 7, Unit: 2, Size: 5, Flag: 1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
AutoExtend: NO, Increment: 0, MaxSize: 0
Initial Area: 2, Tail: 5, First: 0, Free: 1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--//當前40K佔5塊.size=tail=5.
Deallocation scn: 925704.0
Header Opcode:
Save: No Pending Op
Block dump from cache:
Dump of buffer cache at level 4 for tsn=9 rdba=29360131
BH (0x73ff8830) file#: 7 rdba: 0x01c00003 (7/3) class: 12 ba: 0x73f8c000
set: 70 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 23,28
dbwrid: 0 obj: -1 objn: 24 tsn: 9 afn: 7 hint: f
hash: [0x84572000,0x84572000] lru: [0x6d7f5d40,0x843d45c0]
lru-flags: hot_buffer
ckptq: [NULL] fileq: [NULL] objq: [0x7c3d3948,0x7c3d3948] objaq: [0x7c3d3938,0x7c3d3938]
st: XCURRENT md: NULL fpin: 'ktfbwh01: ktfbbfmt' tch: 0
flags: foreground_waiting block_written_once redo_since_read
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1]
Block dump from disk:
buffer tsn: 9 rdba: 0x01c00003 (7/3)
scn: 0x0003.177527e3 seq: 0x01 flg: 0x04 tail: 0x27e31e01
frmt: 0x02 chkval: 0x56a8 type: 0x1e=KTFB Bitmapped File Space Bitmap
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F1EBA962200 to 0x00007F1EBA964200
7F1EBA962200 0000A21E 01C00003 177527E3 04010003 [.........'u.....]
7F1EBA962210 000056A8 00000007 00000004 00000000 [.V..............]
7F1EBA962220 00000000 0000F800 00000000 00000000 [................]
7F1EBA962230 00000000 00000000 00000000 00000000 [................]
Repeat 507 times
7F1EBA9641F0 00000000 00000000 00000000 27E31E01 [...............']
File Space Bitmap Block:
BitMap Control:
RelFno: 7, BeginBlock: 4, Flag: 0, First: 0, Free: 63488
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--//注意下劃線,BeginBlock=4.free=63488.

3.增大資料檔案測試:
--//資料最大容納992M(1個點陣圖塊),該資料檔案開頭佔3塊(檔案頭,點陣圖區頭,點陣圖區1塊).
--//如果建立992*1024+3*8 = 1015832K

SCOTT@book> ALTER DATABASE DATAFILE '/mnt/ramdisk/book/T01.dbf' RESIZE 1015832K;
Database altered.

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

Start dump data blocks tsn: 9 file#:7 minblk 2 maxblk 3
Block dump from cache:
Dump of buffer cache at level 4 for tsn=9 rdba=29360130
BH (0x677f6fd0) file#: 7 rdba: 0x01c00002 (7/2) class: 13 ba: 0x67764000
set: 71 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,25
dbwrid: 1 obj: -1 objn: 24 tsn: 9 afn: 7 hint: f
hash: [0x84b95f68,0x84b95f68] lru: [0x743f7a80,0x783e5d50]
lru-flags: hot_buffer
obj-flags: object_ckpt_list
ckptq: [0x83e62980,0x83e62980] fileq: [0x83e62a60,0x83e62a60] objq: [0x7c18a120,0x7c18a120] objaq: [0x7c18a100,0x7c18a100]
st: XCURRENT md: NULL fpin: 'ktfbwh00: ktfbhfmt' tch: 34
flags: buffer_dirty foreground_waiting block_written_once redo_since_read
LRBA: [0x3bc.b7ac.0] LSCN: [0x3.17762140] HSCN: [0x3.17762141] HSUB: [1]
Block dump from disk:
buffer tsn: 9 rdba: 0x01c00002 (7/2)
scn: 0x0003.1776213c seq: 0xfb flg: 0x04 tail: 0x213c1dfb
frmt: 0x02 chkval: 0x6c1f type: 0x1d=KTFB Bitmapped File Space Header
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007FDB20DD1800 to 0x00007FDB20DD3800
7FDB20DD1800 0000A21D 01C00002 1776213C 04FB0003 [........<!v.....]
7FDB20DD1810 00006C1F 00000007 00000002 0001F003 [.l..............]
7FDB20DD1820 00000001 00000000 00000000 00000002 [................]
7FDB20DD1830 0001F003 00000001 0000F7FF 1776213C [............<!v.]
7FDB20DD1840 00000003 00000000 00000000 00000000 [................]
7FDB20DD1850 00000006 00000002 00000000 00000000 [................]
7FDB20DD1860 00000000 00000000 00000000 00000000 [................]
Repeat 504 times
7FDB20DD37F0 00000000 00000000 00000000 213C1DFB [..............<!]
File Space Header Block:
Header Control:
RelFno: 7, Unit: 2, Size: 126979, Flag: 1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
AutoExtend: NO, Increment: 0, MaxSize: 0
Initial Area: 2, Tail: 126979, First: 1, Free: 63487
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--//注意下劃線,size=tail=126979.

Deallocation scn: 393617724.3
Header Opcode:
Save: No Pending Op
Block dump from cache:
Dump of buffer cache at level 4 for tsn=9 rdba=29360131
BH (0x73ff8830) file#: 7 rdba: 0x01c00003 (7/3) class: 12 ba: 0x73f8c000
set: 70 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,25
dbwrid: 0 obj: -1 objn: 24 tsn: 9 afn: 7 hint: f
hash: [0x84572000,0x84572000] lru: [0x71bf1078,0x783e43b8]
lru-flags: hot_buffer
obj-flags: object_ckpt_list
ckptq: [0x83e4ad48,0x747e4670] fileq: [0x83e4ae28,0x83e4ae28] objq: [0x7c3d3958,0x7c3d3958] objaq: [0x7c3d3938,0x7c3d3938]
st: XCURRENT md: NULL fpin: 'ktfbwh01: ktfbbfmt' tch: 17
flags: buffer_dirty foreground_waiting block_written_once redo_since_read
LRBA: [0x3bc.b7ac.0] LSCN: [0x3.17762140] HSCN: [0x3.17762140] HSUB: [1]
Block dump from disk:
buffer tsn: 9 rdba: 0x01c00003 (7/3)
scn: 0x0003.1776213c seq: 0x7d flg: 0x04 tail: 0x213c1e7d
frmt: 0x02 chkval: 0x5954 type: 0x1e=KTFB Bitmapped File Space Bitmap
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007FDB20DD1800 to 0x00007FDB20DD3800
7FDB20DD1800 0000A21E 01C00003 1776213C 047D0003 [........<!v...}.]
7FDB20DD1810 00005954 00000007 00000004 00000000 [TY..............]
7FDB20DD1820 00000001 0000F7FF 00000000 00000000 [................]
7FDB20DD1830 00000000 00000000 00000001 00000000 [................]
7FDB20DD1840 00000000 00000000 00000000 00000000 [................]
Repeat 506 times
7FDB20DD37F0 00000000 00000000 00000000 213C1E7D [............}.<!]
File Space Bitmap Block:
BitMap Control:
RelFno: 7, BeginBlock: 4, Flag: 0, First: 1, Free: 63487

--//增加2個區看看 1015832+2*16 = 1015864;

SCOTT@book> ALTER DATABASE DATAFILE '/mnt/ramdisk/book/T01.dbf' RESIZE 1015864K;
Database altered.

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

Start dump data blocks tsn: 9 file#:7 minblk 2 maxblk 3
Block dump from cache:
Dump of buffer cache at level 4 for tsn=9 rdba=29360130
BH (0x677f6fd0) file#: 7 rdba: 0x01c00002 (7/2) class: 13 ba: 0x67764000
set: 71 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,25
dbwrid: 1 obj: -1 objn: 24 tsn: 9 afn: 7 hint: f
hash: [0x84b95f68,0x84b95f68] lru: [0x733d80b8,0x783e5d50]
lru-flags: hot_buffer
ckptq: [NULL] fileq: [NULL] objq: [0x7c18a110,0x7c18a110] objaq: [0x7c18a100,0x7c18a100]
st: XCURRENT md: NULL fpin: 'ktfbwh00: ktfbhfmt' tch: 37
flags: foreground_waiting block_written_once redo_since_read
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1]
Block dump from disk:
buffer tsn: 9 rdba: 0x01c00002 (7/2)
scn: 0x0003.17762249 seq: 0x01 flg: 0x04 tail: 0x22491d01
frmt: 0x02 chkval: 0x93e3 type: 0x1d=KTFB Bitmapped File Space Header
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F0B20EFFA00 to 0x00007F0B20F01A00
7F0B20EFFA00 0000A21D 01C00002 17762249 04010003 [........I"v.....]
7F0B20EFFA10 000093E3 00000007 00000002 0001F007 [................]
7F0B20EFFA20 00000001 00000000 00000000 00000002 [................]
7F0B20EFFA30 0001F005 00000000 0000F801 1776213F [............?!v.]
7F0B20EFFA40 00000003 00000000 00000000 00000000 [................]
7F0B20EFFA50 0001F006 00000001 00000000 00000000 [................]
7F0B20EFFA60 00000000 00000000 00000000 00000000 [................]
Repeat 504 times
7F0B20F019F0 00000000 00000000 00000000 22491D01 [..............I"]
File Space Header Block:
Header Control:
RelFno: 7, Unit: 2, Size: 126983, Flag: 1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
AutoExtend: NO, Increment: 0, MaxSize: 0
Initial Area: 2, Tail: 126981, First: 0, Free: 63489
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--//size=126983,tail=126981.相差2塊.

Deallocation scn: 393617727.3
Header Opcode:
Save: No Pending Op
Block dump from cache:
Dump of buffer cache at level 4 for tsn=9 rdba=29360131
BH (0x73ff8830) file#: 7 rdba: 0x01c00003 (7/3) class: 12 ba: 0x73f8c000
set: 70 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,25
dbwrid: 0 obj: -1 objn: 24 tsn: 9 afn: 7 hint: f
hash: [0x84572000,0x84572000] lru: [0x72fd8ce8,0x783e43b8]
lru-flags: hot_buffer
ckptq: [NULL] fileq: [NULL] objq: [0x7c3d3948,0x7c3d3948] objaq: [0x7c3d3938,0x7c3d3938]
st: XCURRENT md: NULL fpin: 'ktfbwh01: ktfbbfmt' tch: 17
flags: foreground_waiting block_written_once redo_since_read
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1]
Block dump from disk:
buffer tsn: 9 rdba: 0x01c00003 (7/3)
scn: 0x0003.17762140 seq: 0x01 flg: 0x04 tail: 0x21401e01
frmt: 0x02 chkval: 0x56ab type: 0x1e=KTFB Bitmapped File Space Bitmap
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F0B20EFFA00 to 0x00007F0B20F01A00
7F0B20EFFA00 0000A21E 01C00003 17762140 04010003 [........@!v.....]
7F0B20EFFA10 000056AB 00000007 00000004 00000000 [.V..............]
7F0B20EFFA20 00000000 0000F800 00000000 00000000 [................]
7F0B20EFFA30 00000000 00000000 00000000 00000000 [................]
Repeat 507 times
7F0B20F019F0 00000000 00000000 00000000 21401E01 [..............@!]
File Space Bitmap Block:
BitMap Control:
RelFno: 7, BeginBlock: 4, Flag: 0, First: 0, Free: 63488

--//轉儲tail+1=126982塊看看:
SCOTT@book> alter system dump datafile 7 block 126982;
System altered.

--//檢查轉儲:
Block dump from disk:
buffer tsn: 9 rdba: 0x01c1f006 (7/126982)
scn: 0x0003.17762247 seq: 0x02 flg: 0x04 tail: 0x22471e02
frmt: 0x02 chkval: 0x56ae type: 0x1e=KTFB Bitmapped File Space Bitmap
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//可以發現這塊就是點陣圖區.
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F0B20EFFA00 to 0x00007F0B20F01A00
7F0B20EFFA00 0000A21E 01C1F006 17762247 04020003 [........G"v.....]
7F0B20EFFA10 000056AE 00000007 0001F004 00000000 [.V..............]
7F0B20EFFA20 00000000 0000F800 00000000 00000000 [................]
7F0B20EFFA30 00000000 00000000 00000000 00000000 [................]
Repeat 507 times
7F0B20F019F0 00000000 00000000 00000000 22471E02 [..............G"]
File Space Bitmap Block:
BitMap Control:
RelFno: 7, BeginBlock: 126980, Flag: 0, First: 0, Free: 63488

--//你可以想像oracle當資料檔案增大,前面點陣圖區空間不夠時,使用資料檔案尾部的塊來儲存點陣圖資訊.
--//這樣資料檔案增大時,就存在一點點副作用就是尾部的點陣圖區要不斷往後移.

4.繼續測試
--//再增加1個區看看 1015832+2*16+16 = 1015880;

SCOTT@book> ALTER DATABASE DATAFILE '/mnt/ramdisk/book/T01.dbf' RESIZE 1015880K;
Database altered.

SCOTT@book> alter system checkpoint ;
System altered.

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

Start dump data blocks tsn: 9 file#:7 minblk 2 maxblk 3
Block dump from cache:
Dump of buffer cache at level 4 for tsn=9 rdba=29360130
BH (0x793d7878) file#: 7 rdba: 0x01c00002 (7/2) class: 13 ba: 0x7902a000
set: 66 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 5,28
dbwrid: 2 obj: -1 objn: 24 tsn: 9 afn: 7 hint: f
hash: [0x84b95f68,0x84b95f68] lru: [0x797eadc0,0x793d76f8]
ckptq: [NULL] fileq: [NULL] objq: [0x7cc39860,0x7cc39860] objaq: [0x7cc39850,0x7cc39850]
st: XCURRENT md: NULL fpin: 'kttewh00: ktte_get_file_info' tch: 4
flags: block_written_once redo_since_read
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1]
Block dump from disk:
buffer tsn: 9 rdba: 0x01c00002 (7/2)
scn: 0x0003.17762cde seq: 0x01 flg: 0x04 tail: 0x2cde1d01
frmt: 0x02 chkval: 0x93e2 type: 0x1d=KTFB Bitmapped File Space Header
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007FD19D0F1A00 to 0x00007FD19D0F3A00
7FD19D0F1A00 0000A21D 01C00002 17762CDE 04010003 [.........,v.....]
7FD19D0F1A10 000093E2 00000007 00000002 0001F009 [................]
7FD19D0F1A20 00000001 00000000 00000000 00000002 [................]
7FD19D0F1A30 0001F007 00000000 0000F802 1776213F [............?!v.]
7FD19D0F1A40 00000003 00000000 00000000 00000000 [................]
7FD19D0F1A50 0001F008 00000001 00000000 00000000 [................]
7FD19D0F1A60 00000000 00000000 00000000 00000000 [................]
Repeat 504 times
7FD19D0F39F0 00000000 00000000 00000000 2CDE1D01 [...............,]
File Space Header Block:
Header Control:
RelFno: 7, Unit: 2, Size: 126985, Flag: 1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
AutoExtend: NO, Increment: 0, MaxSize: 0
Initial Area: 2, Tail: 126983, First: 0, Free: 63490
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--//size=126985,tail=126983,相差2塊.注意觀察尾部比前面126981多2.
--//這樣點陣圖區塊126984.

SCOTT@book> alter system dump datafile 7 block 126984;
System altered.

Block dump from disk:
buffer tsn: 9 rdba: 0x01c1f008 (7/126984)
scn: 0x0003.17762cde seq: 0x01 flg: 0x04 tail: 0x2cde1e01
frmt: 0x02 chkval: 0x56a0 type: 0x1e=KTFB Bitmapped File Space Bitmap
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//可以發現這塊就是點陣圖區.
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007FD19D0F1A00 to 0x00007FD19D0F3A00
7FD19D0F1A00 0000A21E 01C1F008 17762CDE 04010003 [.........,v.....]
7FD19D0F1A10 000056A0 00000007 0001F004 00000000 [.V..............]
7FD19D0F1A20 00000000 0000F800 00000000 00000000 [................]
7FD19D0F1A30 00000000 00000000 00000000 00000000 [................]
Repeat 507 times
7FD19D0F39F0 00000000 00000000 00000000 2CDE1E01 [...............,]
File Space Bitmap Block:
BitMap Control:
RelFno: 7, BeginBlock: 126980, Flag: 0, First: 0, Free: 63488

--//可以發現點陣圖區往後移,所以在10g上,如果資料檔案有不斷變大的趨勢,建議設定固定值,關閉AUTOEXTEND.

5.這樣存在另外一個問題,假設資料檔案變大,使用點陣圖區不再是2塊,而是3塊.(針對當前的例子).

--//這樣資料檔案尾部存在3塊儲存(甚至更多點陣圖塊的情況),如果減少1塊.這樣會出現什麼情況呢?

--//假設建立資料檔案大小3*1024*1024,按照前面介紹1個點陣圖區儲存992M(注意我這裡1extent=16K).這樣需要4個點陣圖區.
--//說明減少8K,這樣對齊邊界.

SCOTT@book> ALTER DATABASE DATAFILE '/mnt/ramdisk/book/T01.dbf' RESIZE 3G;
Database altered.

SCOTT@book> alter system checkpoint ;
System altered.

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

Block dump from disk:
buffer tsn: 9 rdba: 0x01c00002 (7/2)
scn: 0x0003.177633d8 seq: 0x01 flg: 0x04 tail: 0x33d81d01
frmt: 0x02 chkval: 0x9be3 type: 0x1d=KTFB Bitmapped File Space Header
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007FB42D56EA00 to 0x00007FB42D570A00
7FB42D56EA00 0000A21D 01C00002 177633D8 04010003 [.........3v.....]
7FB42D56EA10 00009BE3 00000007 00000002 0003FFFE [................]
7FB42D56EA20 00000001 00000000 00000000 00000002 [................]
7FB42D56EA30 0003FFFB 00000000 0001FFFC 1776213F [............?!v.]
7FB42D56EA40 00000003 00000000 00000000 00000000 [................]
7FB42D56EA50 0003FFFC 00000002 00000000 00000000 [................]
7FB42D56EA60 00000000 00000000 00000000 00000000 [................]
Repeat 504 times
7FB42D5709F0 00000000 00000000 00000000 33D81D01 [...............3]
File Space Header Block:
Header Control:
RelFno: 7, Unit: 2, Size: 262142, Flag: 1
AutoExtend: NO, Increment: 0, MaxSize: 0
Initial Area: 2, Tail: 262139, First: 0, Free: 131068
Deallocation scn: 393617727.3
Header Opcode:
Save: No Pending Op

--//262140,262141,262142塊是點陣圖區.如果我減少資料檔案8K(1塊)呢?

SCOTT@book> select 3*1024*1024-8 from dual ;
3*1024*1024-8
-------------
3145720

SCOTT@book> ALTER DATABASE DATAFILE '/mnt/ramdisk/book/T01.dbf' RESIZE 3145720K;
ALTER DATABASE DATAFILE '/mnt/ramdisk/book/T01.dbf' RESIZE 3145720K
*
ERROR at line 1:
ORA-03215: File Size specified for resize is too small

--//增大OK,減少報錯.

$ oerr ora 03215
03215, 00000, "File Size specified for resize is too small "
// *Cause: File Size specified for resize datafile/tempfile causes
// bitmap control structures to overlap
// *Action: Increase the specification for file size

--//注意看提示File Size specified for resize datafile/tempfile causes bitmap control structures to overlap.
--//說明點陣圖區出現overlap.要減少在這個例子必須減少3*8K以上.避開overlap區間.

SCOTT@book> ALTER DATABASE DATAFILE '/mnt/ramdisk/book/T01.dbf' RESIZE 3145712K;
ALTER DATABASE DATAFILE '/mnt/ramdisk/book/T01.dbf' RESIZE 3145712K
*
ERROR at line 1:
ORA-03215: File Size specified for resize is too small

SCOTT@book> ALTER DATABASE DATAFILE '/mnt/ramdisk/book/T01.dbf' RESIZE 3145704K;
Database altered.

SCOTT@book> select 3*1024*1024-3*8 from dual ;
3*1024*1024-3*8
---------------
3145704

--//你可以想像只要不出現點陣圖區的overlap,相同大小根本不會報錯.

SCOTT@book> ALTER DATABASE DATAFILE '/mnt/ramdisk/book/T01.dbf' RESIZE 1G;
Database altered.

SCOTT@book> ALTER DATABASE DATAFILE '/mnt/ramdisk/book/T01.dbf' RESIZE 3145712K;
Database altered.

6.另外方法確定點陣圖區位置:
--//可以參考連結http://blog.itpub.net/267265/viewspace-1399275/
--//檢視檢視dba_free_space的底層檢視,可以知道訪問sys.x$ktfbfe.要確定訪問那些塊必須flush BUFFER_CACHE;

SYS@book> alter system flush BUFFER_CACHE;
System altered.

SYS@book> @ &r/10046on 12
Session altered.

SYS@book> select * from sys.x$ktfbfe where ktfbfefno=7;
ADDR                   INDX    INST_ID KTFBFETSN KTFBFEFNO KTFBFEBNO KTFBFEBLKS
---------------- ---------- ---------- ---------- ---------- ---------- ----------
00007FD54C4F11F8         93          1          9          7          4     126976
00007FD54C4F11F8         94          1          9          7     126980     126976
00007FD54C4F11F8         95          1          9          7     253956     126976
00007FD54C4F11F8         96          1          9          7     380932      12280

SYS@book> @ &r/10046off
Session altered.

=====================
PARSING IN CURSOR #140554085077960 len=100 dep=1 uid=0 oct=3 lid=0 tim=1512612168524518 hv=3768030067 ad='7d7696a8' sqlid='3fkaxqzh9g4vm'
select ts#, flags from ts$ where bitmapped <> 0 and contents$ = 0 and (online$ = 1 or online$ = 4)
END OF STMT
PARSE #140554085077960:c=1999,e=1320,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=3778488125,tim=1512612168524517
EXEC #140554085077960:c=0,e=24,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3778488125,tim=1512612168524640
WAIT #140554085077960: nam='db file sequential read' ela= 28 file#=1 block#=176 blocks=1 obj#=16 tim=1512612168524750
WAIT #140554085077960: nam='db file scattered read' ela= 43 file#=1 block#=177 blocks=7 obj#=16 tim=1512612168524980
FETCH #140554085077960:c=0,e=414,p=8,cr=3,cu=0,mis=0,r=1,dep=1,og=4,plh=3778488125,tim=1512612168525079
WAIT #140554085090328: nam='db file sequential read' ela= 11 file#=1 block#=2 blocks=1 obj#=-1 tim=1512612168525168
WAIT #140554085090328: nam='db file sequential read' ela= 9 file#=1 block#=3 blocks=1 obj#=-1 tim=1512612168525217
FETCH #140554085077960:c=0,e=19,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=4,plh=3778488125,tim=1512612168525327
WAIT #140554085090328: nam='Disk file operations I/O' ela= 45 FileOperation=2 fileno=2 filetype=2 obj#=-1 tim=1512612168525432
WAIT #140554085090328: nam='db file sequential read' ela= 13 file#=2 block#=2 blocks=1 obj#=-1 tim=1512612168525478
WAIT #140554085090328: nam='db file sequential read' ela= 9 file#=2 block#=3 blocks=1 obj#=-1 tim=1512612168525520
FETCH #140554085077960:c=0,e=15,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=4,plh=3778488125,tim=1512612168525666
WAIT #140554085090328: nam='db file sequential read' ela= 11 file#=3 block#=2 blocks=1 obj#=-1 tim=1512612168525731
WAIT #140554085090328: nam='db file sequential read' ela= 8 file#=3 block#=3 blocks=1 obj#=-1 tim=1512612168525792
FETCH #140554085077960:c=0,e=18,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=3778488125,tim=1512612168525939
WAIT #140554085090328: nam='db file sequential read' ela= 13 file#=4 block#=2 blocks=1 obj#=-1 tim=1512612168526014
WAIT #140554085090328: nam='db file sequential read' ela= 9 file#=4 block#=3 blocks=1 obj#=-1 tim=1512612168526059
FETCH #140554085077960:c=0,e=14,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=3778488125,tim=1512612168526125
WAIT #140554085090328: nam='db file sequential read' ela= 12 file#=5 block#=2 blocks=1 obj#=-1 tim=1512612168526191
WAIT #140554085090328: nam='db file sequential read' ela= 9 file#=5 block#=3 blocks=1 obj#=-1 tim=1512612168526269
WAIT #140554085077960: nam='db file scattered read' ela= 33 file#=1 block#=94672 blocks=8 obj#=16 tim=1512612168526415
FETCH #140554085077960:c=0,e=154,p=8,cr=1,cu=0,mis=0,r=1,dep=1,og=4,plh=3778488125,tim=1512612168526481
WAIT #140554085090328: nam='db file sequential read' ela= 10 file#=6 block#=2 blocks=1 obj#=-1 tim=1512612168526547
WAIT #140554085090328: nam='db file sequential read' ela= 8 file#=6 block#=3 blocks=1 obj#=-1 tim=1512612168526592
FETCH #140554085077960:c=0,e=17,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=3778488125,tim=1512612168526646
WAIT #140554085090328: nam='db file sequential read' ela= 12 file#=7 block#=2 blocks=1 obj#=-1 tim=1512612168526708
WAIT #140554085090328: nam='db file sequential read' ela= 9 file#=7 block#=3 blocks=1 obj#=-1 tim=1512612168526763
FETCH #140554085090328:c=3998,e=3873,p=30,cr=12,cu=20,mis=0,r=1,dep=0,og=1,plh=564333037,tim=1512612168527029
WAIT #140554085090328: nam='SQL*Net message from client' ela= 486 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1512612168527574
WAIT #140554085090328: nam='db file sequential read' ela= 14 file#=7 block#=393212 blocks=1 obj#=-1 tim=1512612168527678
WAIT #140554085090328: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1512612168527969
WAIT #140554085090328: nam='db file sequential read' ela= 10 file#=7 block#=393213 blocks=1 obj#=-1 tim=1512612168528026
WAIT #140554085090328: nam='db file sequential read' ela= 9 file#=7 block#=393214 blocks=1 obj#=-1 tim=1512612168528290
FETCH #140554085077960:c=0,e=31,p=0,cr=5,cu=0,mis=0,r=0,dep=1,og=4,plh=3778488125,tim=1512612168528384
STAT #140554085077960 id=1 cnt=7 pid=0 pos=1 obj=16 op='TABLE ACCESS FULL TS$ (cr=17 pr=16 pw=0 time=408 us cost=6 size=216 card=12)'
CLOSE #140554085077960:c=0,e=6,dep=1,type=0,tim=1512612168528536
FETCH #140554085090328:c=1000,e=937,p=3,cr=5,cu=7,mis=0,r=3,dep=0,og=1,plh=564333037,tim=1512612168528573
STAT #140554085090328 id=1 cnt=4 pid=0 pos=1 obj=0 op='FIXED TABLE FULL X$KTFBFE (cr=17 pr=33 pw=0 time=3867 us cost=0 size=84 card=1)'
*** 2017-12-07 10:02:54.553
WAIT #140554085090328: nam='SQL*Net message from client' ela= 6024941 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1512612174553592
CLOSE #140554085090328:c=0,e=21,dep=0,type=0,tim=1512612174553838
=====================

$ grep 'db file sequential read' /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_11100.trc |grep "file#=7"^J
WAIT #140554085090328: nam='db file sequential read' ela= 12 file#=7 block#=2 blocks=1 obj#=-1 tim=1512612168526708
WAIT #140554085090328: nam='db file sequential read' ela= 9 file#=7 block#=3 blocks=1 obj#=-1 tim=1512612168526763
WAIT #140554085090328: nam='db file sequential read' ela= 14 file#=7 block#=393212 blocks=1 obj#=-1 tim=1512612168527678
WAIT #140554085090328: nam='db file sequential read' ela= 10 file#=7 block#=393213 blocks=1 obj#=-1 tim=1512612168528026
WAIT #140554085090328: nam='db file sequential read' ela= 9 file#=7 block#=393214 blocks=1 obj#=-1 tim=1512612168528290

--//可以確定2,3,393212,393213,393214是點陣圖區.
--//資料檔案大小3145712/8 = 393214塊,最後3塊就是點陣圖區.

--//總結:2015年前就分析過,有點遺忘了,重新做一次學習.

[20171206]點陣圖區一定在資料檔案開頭嗎.txt

相關文章