[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年前就分析过,有点遗忘了,重新做一次学习.
