[20150112]系统管理表空间的疑问.txt
http://www.itpub.net/thread-1903121-1-1.html
10g下每个数据文件3－8块为位图区。
6个块＝48K（假设数据块大小8k）
48*8*1024＝393216 区
对于系统表空间管理。1区＝64K。
SCOTT@test> set numw 20
SCOTT@test> select 48*8*1024*64*1024 from dual;
48*8*1024*64*1024
--------------------
25769803776
--仅仅相当于24G，而实际文件可以达到32G（对于8k的数据块），我到底那里分析错了。实际上这个问题一致困扰我，今天有空做一个分析。
--正好发现视图dba_free_space，如果查看定义可以发现分成3个部分，第1部分输出是字典管理表空间的，应该在许多系统没有输出，因
--为不会再采用字典管理方式，第3部分是涉及一个sys.recyclebin$表，很明显这个部分是指drop后没有回收的空间。
--而第2部分如下：
SELECT /*+ ordered use_nl(f) use_nl(fi) */
ts.name,
fi.file#,
f.ktfbfebno,
f.ktfbfeblks * ts.blocksize,
f.ktfbfeblks,
f.ktfbfefno
FROM sys.ts$ ts, sys.x$ktfbfe f, sys.file$ fi
WHERE ts.ts# = f.ktfbfetsn
AND f.ktfbfetsn = fi.ts#
AND f.ktfbfefno = fi.relfile#
AND ts.bitmapped 0
AND ts.online$ IN (1, 4)
AND ts.contents$ = 0;
--可以大致推出只要扫描sys.x$ktfbfe就可以知道存在那些free空间。而本质也可以推出就是扫描位图区。
--首先先清除buffer_cache.
SYS> alter system flush BUFFER_CACHE;
System altered.
--使用10046跟踪，并且找一个数据文件最好是写满的大小32G(8k数据块)，我这里选择file#＝53号文件。
SYS@XXXXX> @ &r/10046on 12
Session altered.
SYS@XXXXX> select * from sys.x$ktfbfe where ktfbfefno=53 and KTFBFETSN=18;
ADDR INDX INST_ID KTFBFETSN KTFBFEFNO KTFBFEBNO KTFBFEBLKS
---------------- ---------- ---------- ---------- ---------- ---------- ----------
00002ADF5C99C078 9 1 18 53 4194185 112
SYS@XXXXX> @ &r/10046off
Session altered.
# grep "file#=53" /u01/app/oracle/admin/XXXXX/udump/XXXXX_ora_27286.trc
WAIT #3: nam='db file sequential read' ela= 22629 file#=53 block#=2 blocks=1 obj#=-1 tim=1387739800835263
WAIT #3: nam='db file sequential read' ela= 32533 file#=53 block#=3 blocks=1 obj#=-1 tim=1387739800867833
WAIT #3: nam='db file sequential read' ela= 8026 file#=53 block#=4 blocks=1 obj#=-1 tim=1387739800876024
WAIT #3: nam='db file sequential read' ela= 13044 file#=53 block#=5 blocks=1 obj#=-1 tim=1387739800889224
WAIT #3: nam='db file sequential read' ela= 8026 file#=53 block#=6 blocks=1 obj#=-1 tim=1387739800897403
WAIT #3: nam='db file sequential read' ela= 278 file#=53 block#=7 blocks=1 obj#=-1 tim=1387739800897835
WAIT #3: nam='db file sequential read' ela= 121 file#=53 block#=8 blocks=1 obj#=-1 tim=1387739800898104
WAIT #3: nam='db file sequential read' ela= 15648 file#=53 block#=4194297 blocks=1 obj#=-1 tim=1387739800913902
WAIT #3: nam='db file sequential read' ela= 9974 file#=53 block#=4194298 blocks=1 obj#=-1 tim=1387739800924029
WAIT #3: nam='db file sequential read' ela= 527 file#=53 block#=4194299 blocks=1 obj#=-1 tim=1387739800924709
--如果你看跟踪文件你可以发现TS#=18的全部扫描。我仅仅过滤file#=53的内容。因为跟踪内容存在如下：
=====================
PARSING IN CURSOR #8 len=36 dep=1 uid=0 oct=3 lid=0 tim=1387723894239979 hv=1570213724 ad='1d90cd90'
select file# from file$ where ts#=:1
END OF STMT
PARSE #8:c=0,e=22,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1387723894239976
BINDS #8:
kkscoacd
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=2b7d658eea20 bln=22 avl=02 flg=05
value=18
--注意看看上面扫描的block，包括2,3,4,5,6,7,8,4194297,4194298,4194299.可以推出这些是位图块。查看另外一个file#=54也可以确
--定。
# grep "file#=54" /u01/app/oracle/admin/XXXXX/udump/XXXXX_ora_27286.trc
WAIT #3: nam='db file sequential read' ela= 11098 file#=54 block#=2 blocks=1 obj#=-1 tim=1387739800936380
WAIT #3: nam='db file sequential read' ela= 8180 file#=54 block#=3 blocks=1 obj#=-1 tim=1387739800944590
WAIT #3: nam='db file sequential read' ela= 278 file#=54 block#=4 blocks=1 obj#=-1 tim=1387739800945024
WAIT #3: nam='db file sequential read' ela= 7738 file#=54 block#=5 blocks=1 obj#=-1 tim=1387739800952913
WAIT #3: nam='db file sequential read' ela= 32415 file#=54 block#=6 blocks=1 obj#=-1 tim=1387739800985477
WAIT #3: nam='db file sequential read' ela= 284 file#=54 block#=7 blocks=1 obj#=-1 tim=1387739800985920
WAIT #3: nam='db file sequential read' ela= 117 file#=54 block#=8 blocks=1 obj#=-1 tim=1387739800986185
WAIT #3: nam='db file sequential read' ela= 4655 file#=54 block#=4194289 blocks=1 obj#=-1 tim=1387739800990992
WAIT #3: nam='db file sequential read' ela= 8065 file#=54 block#=4194290 blocks=1 obj#=-1 tim=1387739800999210
WAIT #3: nam='db file sequential read' ela= 272 file#=54 block#=4194291 blocks=1 obj#=-1 tim=1387739800999637
--我们做一个转储就可以确定我们的判断是否正确。注意看最后3行，file#＝53以及file#＝54的位图位置所在的块不一样。
SYS@XXXXX> alter system dump datafile 53 block min 4194297 block max 4194299;
System altered.
Start dump data blocks tsn: 18 file#: 53 minblk 4194297 maxblk 4194299
buffer tsn: 18 rdba: 0x0d7ffff9 (53/4194297)
scn: 0x0002.dafd7d05 seq: 0x01 flg: 0x04 tail: 0x7d051e01
frmt: 0x02 chkval: 0xe875 type: 0x1e=KTFB Bitmapped File Space Bitmap
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0000000015459400 to 0x000000001545B400
015459400 0000A21E 0D7FFFF9 DAFD7D05 04010002 [.........}......]
015459410 0000E875 00000035 002E8009 00000000 [u...5...........]
015459420 0000F800 00000000 00000000 00000000 [................]
015459430 00000000 00000000 FFFFFFFF FFFFFFFF [................]
015459440 FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF [................]
Repeat 494 times
01545B330 FFFFFFFF FFFFFFFF 00000000 00000000 [................]
01545B340 00000000 00000000 00000000 00000000 [................]
Repeat 10 times
01545B3F0 00000000 00000000 00000000 7D051E01 [...............}]
File Space Bitmap Block:
BitMap Control:
RelFno: 53, BeginBlock: 3047433, Flag: 0, First: 63488, Free: 0
FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF
...
FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF
buffer tsn: 18 rdba: 0x0d7ffffa (53/4194298)
scn: 0x0002.dafd7d05 seq: 0x01 flg: 0x04 tail: 0x7d051e01
frmt: 0x02 chkval: 0x286e type: 0x1e=KTFB Bitmapped File Space Bitmap
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0000000015459400 to 0x000000001545B400
015459400 0000A21E 0D7FFFFA DAFD7D05 04010002 [.........}......]
015459410 0000286E 00000035 00364009 00000000 [n(..5....@6.....]
015459420 0000F800 00000000 00000000 00000000 [................]
015459430 00000000 00000000 FFFFFFFF FFFFFFFF [................]
015459440 FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF [................]
Repeat 494 times
01545B330 FFFFFFFF FFFFFFFF 00000000 00000000 [................]
01545B340 00000000 00000000 00000000 00000000 [................]
Repeat 10 times
01545B3F0 00000000 00000000 00000000 7D051E01 [...............}]
File Space Bitmap Block:
BitMap Control:
RelFno: 53, BeginBlock: 3555337, Flag: 0, First: 63488, Free: 0
FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF
...
FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF
buffer tsn: 18 rdba: 0x0d7ffffb (53/4194299)
scn: 0x0002.dafd7d05 seq: 0x01 flg: 0x04 tail: 0x7d051e01
frmt: 0x02 chkval: 0xe878 type: 0x1e=KTFB Bitmapped File Space Bitmap
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0000000015459400 to 0x000000001545B400
015459400 0000A21E 0D7FFFFB DAFD7D05 04010002 [.........}......]
015459410 0000E878 00000035 003E0009 00000000 [x...5.....>.....]
015459420 00003FF0 0000B810 00000000 00000000 [.?..............]
015459430 00000000 00000000 FFFFFFFF FFFFFFFF [................]
015459440 FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF [................]
Repeat 126 times
015459C30 FFFFFFFF 0000FFFF 00000000 00000000 [................]
015459C40 00000000 00000000 00000000 00000000 [................]
Repeat 378 times
01545B3F0 00000000 00000000 00000000 7D051E01 [...............}]
File Space Bitmap Block:
BitMap Control:
RelFno: 53, BeginBlock: 4063241, Flag: 0, First: 16368, Free: 47120
FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF
...
0000000000000000 0000000000000000 0000000000000000 0000000000000000
End dump data blocks tsn: 18 file#: 53 minblk 4194297 maxblk 4194299
--从这里还可以看出位图块并不是完全保存位图信息，仅仅保存了
--(494+2)*32*4= 63488 区（一个块，注意我使用8k的数据块，以下不再说明)
--满的块是3，4，5，6，7，8，4194297,4194298 共8块
8*63488*64=32505856 K
--实际上RelFno: 53, BeginBlock: 3047433, Flag: 0, First: 63488, Free: 0也可以看出来。
最后一块4194299，
127*32*4=16256, 剩下28*4=112
16256+112=16368 区
16368*64=1047552 K
相加
32505856+1047552=33553408
33553408*1024=34358689792 字节
--也就是这个数据文件使用了34358689792字节保存数据。
SYS@XXXXX> select * from dba_free_space where file_id=53;
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------------------------------ ---------- ---------- ---------- ---------- ------------
XXXXX_CHECK_LIS 53 4194185 917504 112 53
--free =112块。 112/8=14区
--使用的加上free：
34358689792+112*8192=34359607296
--加上os文件头1块+8块(1块头，2－8位图区)+3块(4194289-4194291) 总共 12块，注意以下的视图BYTE并不包括OS块。实际是11块。
34359607296+11*8192=34359697408
SYS@XXXXX> set numw 12
SYS@XXXXX> select * from dba_data_files where file_id=53;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_
------------------------------------------------------------ ------------ ------------------------------ ------------ ------------ --------- ------------ --- ------------ ------------ ------------ ------------ ------------ -------
+G0/XXXXX/datafile/XXXXX_check_lis.802.841421175 53 XXXXX_CHECK_LIS 34359730176 4194303 AVAILABLE 53 NO 0 0 0 34359607296 4194288 ONLINE
34359730176-34359697408=32768
32768/8192=4
--奇怪有4个(8k)的差距，why？
--我建立的大小是33554424K。33554424*1024 =34359730176.
32*1024*1024=33554432
os块头占8k，位图块头1块，6+3个位图区
33554432-12*8=33554336
33554336/64=524286.5
--无法整除有32K空间无法利用的。正好4个8k。也就是讲如果节约空间， 32*1024*1024-5*8=33554392K,建立33554392K 一点也不浪费。
SYS@XXXXX> alter system dump datafile 53 block 2 ;
System altered.
*** 2015-01-12 12:00:22.939
Start dump data blocks tsn: 18 file#: 53 minblk 2 maxblk 2
buffer tsn: 18 rdba: 0x0d400002 (53/2)
scn: 0x0002.dafd7d05 seq: 0x01 flg: 0x04 tail: 0x7d051d01
frmt: 0x02 chkval: 0xcc0c type: 0x1d=KTFB Bitmapped File Space Header
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0000000008A8A400 to 0x0000000008A8C400
008A8A400 0000A21D 0D400002 DAFD7D05 04010002 [......@..}......]
008A8A410 0000CC0C 00000035 00000008 003FFFFF [....5.........?.]
008A8A420 00000001 00000000 00000000 00000007 [................]
008A8A430 003FFFF8 0007FFF0 0000000E 00000000 [..?.............]
008A8A440 00000000 00000000 00000000 A0AC0000 [................]
008A8A450 003FFFF9 00000003 00000000 00000000 [..?.............]
008A8A460 00000000 00000000 00000000 00000000 [................]
Repeat 504 times
008A8C3F0 00000000 00000000 00000000 7D051D01 [...............}]
File Space Header Block:
Header Control:
RelFno: 53, Unit: 8, Size: 4194303, Flag: 1
AutoExtend: NO, Increment: 0, MaxSize: 0
Initial Area: 7, Tail: 4194296, First: 524272, Free: 14
Deallocation scn: 0.0
Header Opcode:
Save: No Pending Op
End dump data blocks tsn: 18 file#: 53 minblk 2 maxblk 2
--我以为这里会有包含4194297-4194299块的地址信息，检查发现没有，但是它包含了Initial Area: 7, Tail: 4194296, First: 524272, Free: 14
8*63488+16368=524272
14*8=112
Initial Area: 7
Tail: 4194296
--估计在建立数据文件时已经指定了。可以做一个猜测：
--select * from dba_data_files where file_id=53;显示BLOCKS=4194303.
--Tail: 4194296
--也就是从块4194297作为剩下的位图区。
4194303-4194296=7
--也就是数据文件的最后的剩下的部分作为位图区。从这里也可以看出仅仅用了3块作为位图区(最后),剩下的4块是无用的。
--把剩下的块转储：
SYS@XXXXX> alter system dump datafile 53 block min 4194300 block max 4194303;
System altered.
Start dump data blocks tsn: 18 file#: 53 minblk 4194300 maxblk 4194303
buffer tsn: 18 rdba: 0x0d7ffffc (53/4194300)
scn: 0x0000.00000000 seq: 0x01 flg: 0x05 tail: 0x00000001
frmt: 0x02 chkval: 0x5583 type: 0x00=unknown
Hex dump of block: st=0, typ_found=0
Dump of memory from 0x0000000015459400 to 0x000000001545B400
015459400 0000A200 0D7FFFFC 00000000 05010000 [................]
015459410 00005583 00000000 00000000 00000000 [.U..............]
015459420 00000000 00000000 00000000 00000000 [................]
Repeat 508 times
01545B3F0 00000000 00000000 00000000 00000001 [................]
Dump of memory from 0x0000000015459414 to 0x000000001545B3FC
015459410 00000000 00000000 00000000 [............]
015459420 00000000 00000000 00000000 00000000 [................]
Repeat 508 times
01545B3F0 00000000 00000000 00000000 [............]
buffer tsn: 18 rdba: 0x0d7ffffd (53/4194301)
scn: 0x0000.00000000 seq: 0x01 flg: 0x05 tail: 0x00000001
frmt: 0x02 chkval: 0x5582 type: 0x00=unknown
Hex dump of block: st=0, typ_found=0
Dump of memory from 0x0000000015459400 to 0x000000001545B400
015459400 0000A200 0D7FFFFD 00000000 05010000 [................]
015459410 00005582 00000000 00000000 00000000 [.U..............]
015459420 00000000 00000000 00000000 00000000 [................]
Repeat 508 times
01545B3F0 00000000 00000000 00000000 00000001 [................]
Dump of memory from 0x0000000015459414 to 0x000000001545B3FC
015459410 00000000 00000000 00000000 [............]
015459420 00000000 00000000 00000000 00000000 [................]
Repeat 508 times
01545B3F0 00000000 00000000 00000000 [............]
buffer tsn: 18 rdba: 0x0d7ffffe (53/4194302)
scn: 0x0000.00000000 seq: 0x01 flg: 0x05 tail: 0x00000001
frmt: 0x02 chkval: 0x5581 type: 0x00=unknown
Hex dump of block: st=0, typ_found=0
Dump of memory from 0x0000000015459400 to 0x000000001545B400
015459400 0000A200 0D7FFFFE 00000000 05010000 [................]
015459410 00005581 00000000 00000000 00000000 [.U..............]
015459420 00000000 00000000 00000000 00000000 [................]
Repeat 508 times
01545B3F0 00000000 00000000 00000000 00000001 [................]
Dump of memory from 0x0000000015459414 to 0x000000001545B3FC
015459410 00000000 00000000 00000000 [............]
015459420 00000000 00000000 00000000 00000000 [................]
Repeat 508 times
01545B3F0 00000000 00000000 00000000 [............]
buffer tsn: 18 rdba: 0x0d7fffff (53/4194303)
scn: 0x0000.00000000 seq: 0x01 flg: 0x05 tail: 0x00000001
frmt: 0x02 chkval: 0x5580 type: 0x00=unknown
Hex dump of block: st=0, typ_found=0
Dump of memory from 0x0000000015459400 to 0x000000001545B400
015459400 0000A200 0D7FFFFF 00000000 05010000 [................]
015459410 00005580 00000000 00000000 00000000 [.U..............]
015459420 00000000 00000000 00000000 00000000 [................]
Repeat 508 times
01545B3F0 00000000 00000000 00000000 00000001 [................]
Dump of memory from 0x0000000015459414 to 0x000000001545B3FC
015459410 00000000 00000000 00000000 [............]
015459420 00000000 00000000 00000000 00000000 [................]
Repeat 508 times
01545B3F0 00000000 00000000 00000000 [............]
End dump data blocks tsn: 18 file#: 53 minblk 4194300 maxblk 4194303
--都是无用的块.
--转储file#=54的block=2也可以得出一样的结论：
SYS@XXXXX> alter system dump datafile 54 block 2;
System altered.
Start dump data blocks tsn: 18 file#: 54 minblk 2 maxblk 2
buffer tsn: 18 rdba: 0x0d800002 (54/2)
scn: 0x0002.d6e3012f seq: 0x02 flg: 0x04 tail: 0x012f1d02
frmt: 0x02 chkval: 0x7e02 type: 0x1d=KTFB Bitmapped File Space Header
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0000000015459400 to 0x000000001545B400
015459400 0000A21D 0D800002 D6E3012F 04020002 [......../.......]
015459410 00007E02 00000036 00000008 003FFFF8 [.~..6.........?.]
015459420 00000001 00000000 00000000 00000007 [................]
015459430 003FFFF0 0007FD30 000002CD 00000000 [..?.0...........]
015459440 00000000 00000000 00000000 00000000 [................]
015459450 003FE589 00000400 00000000 00000000 [..?.............]
015459460 00000000 00000000 00000000 00000000 [................]
Repeat 504 times
01545B3F0 00000000 00000000 00000000 012F1D02 [............../.]
File Space Header Block:
Header Control:
RelFno: 54, Unit: 8, Size: 4194296, Flag: 1
AutoExtend: NO, Increment: 0, MaxSize: 0
Initial Area: 7, Tail: 4194288, First: 523568, Free: 717
Deallocation scn: 0.0
Header Opcode:
Save: No Pending Op
End dump data blocks tsn: 18 file#: 54 minblk 2 maxblk 2
--块4194289就是位图区。看看这个的输出就知道了:
# grep "file#=54" /u01/app/oracle/admin/XXXXX/udump/XXXXX_ora_27286.trc
WAIT #3: nam='db file sequential read' ela= 11098 file#=54 block#=2 blocks=1 obj#=-1 tim=1387739800936380
WAIT #3: nam='db file sequential read' ela= 8180 file#=54 block#=3 blocks=1 obj#=-1 tim=1387739800944590
WAIT #3: nam='db file sequential read' ela= 278 file#=54 block#=4 blocks=1 obj#=-1 tim=1387739800945024
WAIT #3: nam='db file sequential read' ela= 7738 file#=54 block#=5 blocks=1 obj#=-1 tim=1387739800952913
WAIT #3: nam='db file sequential read' ela= 32415 file#=54 block#=6 blocks=1 obj#=-1 tim=1387739800985477
WAIT #3: nam='db file sequential read' ela= 284 file#=54 block#=7 blocks=1 obj#=-1 tim=1387739800985920
WAIT #3: nam='db file sequential read' ela= 117 file#=54 block#=8 blocks=1 obj#=-1 tim=1387739800986185
WAIT #3: nam='db file sequential read' ela= 4655 file#=54 block#=4194289 blocks=1 obj#=-1 tim=1387739800990992
WAIT #3: nam='db file sequential read' ela= 8065 file#=54 block#=4194290 blocks=1 obj#=-1 tim=1387739800999210
WAIT #3: nam='db file sequential read' ela= 272 file#=54 block#=4194291 blocks=1 obj#=-1 tim=1387739800999637
--接下来一个问题，如果改变数据文件的大小，位图区可能会跟着移动。按照前面的推测应该会的。
--继续测试,修改数据文件大小file#=54看看,当前33554368K=>33554392k,33554392-33554368=24(增加3x8K).
SYS@XXXXX> ALTER DATABASE DATAFILE 54 RESIZE 33554392K;
Database altered.
Start dump data blocks tsn: 18 file#: 54 minblk 2 maxblk 2
buffer tsn: 18 rdba: 0x0d800002 (54/2)
scn: 0x0002.db140288 seq: 0x01 flg: 0x00 tail: 0x02881d01
frmt: 0x02 chkval: 0x0000 type: 0x1d=KTFB Bitmapped File Space Header
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0000000015459400 to 0x000000001545B400
015459400 0000A21D 0D800002 DB140288 00010002 [................]
015459410 00000000 00000036 00000008 003FFFFB [....6.........?.]
015459420 00000001 00000000 00000000 00000007 [................]
015459430 003FFFF8 0007FD30 000002CE 00000000 [..?.0...........]
015459440 00000000 00000000 00000000 E47E0000 [..............~.]
015459450 003FFFF9 00000003 00000000 00000000 [..?.............]
015459460 00000000 00000000 00000000 00000000 [................]
Repeat 504 times
01545B3F0 00000000 00000000 00000000 02881D01 [................]
File Space Header Block:
Header Control:
RelFno: 54, Unit: 8, Size: 4194299, Flag: 1
AutoExtend: NO, Increment: 0, MaxSize: 0
Initial Area: 7, Tail: 4194296, First: 523568, Free: 718
Deallocation scn: 0.0
Header Opcode:
Save: No Pending Op
End dump data blocks tsn: 18 file#: 54 minblk 2 maxblk 2
--可以发现Initial Area: 7, Tail: 4194296, First: 523568, Free: 718，tail增加了8。free增加了1.
SYS@XXXXX> alter system flush BUFFER_CACHE;
System altered.
SYS@XXXXX> @ &r/10046on 12
Session altered.
SYS@XXXXX> select * from sys.x$ktfbfe where ktfbfefno=54;
ADDR INDX INST_ID KTFBFETSN KTFBFEFNO KTFBFEBNO KTFBFEBLKS
---------------- ---------- ---------- ---------- ---------- ---------- ----------
00002AB8C8084860 514 1 18 54 4188553 5744
SYS@XXXXX> @ &r/10046off
Session altered.
# grep "file#=54" XXXXX_ora_27362.trc
WAIT #3: nam='db file sequential read' ela= 4642 file#=54 block#=2 blocks=1 obj#=-1 tim=1387743573413540
WAIT #3: nam='db file sequential read' ela= 8186 file#=54 block#=3 blocks=1 obj#=-1 tim=1387743573421752
WAIT #3: nam='db file sequential read' ela= 273 file#=54 block#=4 blocks=1 obj#=-1 tim=1387743573422177
WAIT #3: nam='db file sequential read' ela= 4214 file#=54 block#=5 blocks=1 obj#=-1 tim=1387743573426540
WAIT #3: nam='db file sequential read' ela= 8045 file#=54 block#=6 blocks=1 obj#=-1 tim=1387743573434735
WAIT #3: nam='db file sequential read' ela= 261 file#=54 block#=7 blocks=1 obj#=-1 tim=1387743573435145
WAIT #3: nam='db file sequential read' ela= 117 file#=54 block#=8 blocks=1 obj#=-1 tim=1387743573435412
WAIT #3: nam='db file sequential read' ela= 96 file#=54 block#=4194297 blocks=1 obj#=-1 tim=1387743573435654
WAIT #3: nam='db file sequential read' ela= 85 file#=54 block#=4194298 blocks=1 obj#=-1 tim=1387743573435885
WAIT #3: nam='db file sequential read' ela= 65 file#=54 block#=4194299 blocks=1 obj#=-1 tim=1387743573436097
--注意看最后3行，发生了变化，位图区后移动到了4194297。
SYS@XXXXX> select * from dba_data_files where file_id=54;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_
------------------------------------------------------------ ------------ ------------------------------ ------------ ------------ --------- ------------ --- ------------ ------------ ------------ ------------ ------------ -------
+G0/XXXXX/datafile/XXXXX_check_lis.20490.852033985 54 XXXXX_CHECK_LIS 34359697408 4194299 AVAILABLE 54 NO 0 0 0 34359607296 4194288 ONLINE
--最后3块是位图区，正好用完，这样空间利用率最高。
--再改回来。
SYS@XXXXX> ALTER DATABASE DATAFILE 54 RESIZE 33554368K;
Database altered.
SYS@XXXXX> alter system flush BUFFER_CACHE;
System altered.
SYS@XXXXX> @ &r/10046on 12
Session altered.
SYS@XXXXX> select * from sys.x$ktfbfe where ktfbfefno=54;
ADDR INDX INST_ID KTFBFETSN KTFBFEFNO KTFBFEBNO KTFBFEBLKS
---------------- ---------- ---------- ---------- ---------- ---------- ----------
00002AB8C8084860 506 1 18 54 4188553 5736
SYS@XXXXX> @ &r/10046off
Session altered.
# grep "file#=54" XXXXX_ora_27362.trc
WAIT #2: nam='db file sequential read' ela= 5482 file#=54 block#=2 blocks=1 obj#=-1 tim=1387744034824231
WAIT #2: nam='db file sequential read' ela= 8186 file#=54 block#=3 blocks=1 obj#=-1 tim=1387744034832441
WAIT #2: nam='db file sequential read' ela= 278 file#=54 block#=4 blocks=1 obj#=-1 tim=1387744034832867
WAIT #2: nam='db file sequential read' ela= 5383 file#=54 block#=5 blocks=1 obj#=-1 tim=1387744034838399
WAIT #2: nam='db file sequential read' ela= 8057 file#=54 block#=6 blocks=1 obj#=-1 tim=1387744034846604
WAIT #2: nam='db file sequential read' ela= 256 file#=54 block#=7 blocks=1 obj#=-1 tim=1387744034847011
WAIT #2: nam='db file sequential read' ela= 114 file#=54 block#=8 blocks=1 obj#=-1 tim=1387744034847273
WAIT #2: nam='db file sequential read' ela= 6751 file#=54 block#=4194289 blocks=1 obj#=-1 tim=1387744034854172
WAIT #2: nam='db file sequential read' ela= 8070 file#=54 block#=4194290 blocks=1 obj#=-1 tim=1387744034862395
WAIT #2: nam='db file sequential read' ela= 257 file#=54 block#=4194291 blocks=1 obj#=-1 tim=1387744034862803
SYS@XXXXX> alter system dump datafile 54 block 2;
System altered.
*** 2015-01-12 16:06:41.493
Start dump data blocks tsn: 18 file#: 54 minblk 2 maxblk 2
buffer tsn: 18 rdba: 0x0d800002 (54/2)
scn: 0x0002.db150296 seq: 0x01 flg: 0x04 tail: 0x02961d01
frmt: 0x02 chkval: 0x90b8 type: 0x1d=KTFB Bitmapped File Space Header
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0000000015459400 to 0x000000001545B400
015459400 0000A21D 0D800002 DB150296 04010002 [................]
015459410 000090B8 00000036 00000008 003FFFF8 [....6.........?.]
015459420 00000001 00000000 00000000 00000007 [................]
015459430 003FFFF0 00000000 000002CD 00000000 [..?.............]
015459440 00000000 00000000 00000000 00000000 [................]
015459450 003FFFF1 00000003 00000000 00000000 [..?.............]
015459460 00000000 00000000 00000000 00000000 [................]
Repeat 504 times
01545B3F0 00000000 00000000 00000000 02961D01 [................]
File Space Header Block:
Header Control:
RelFno: 54, Unit: 8, Size: 4194296, Flag: 1
AutoExtend: NO, Increment: 0, MaxSize: 0
Initial Area: 7, Tail: 4194288, First: 0, Free: 717
Deallocation scn: 0.0
Header Opcode:
Save: No Pending Op
End dump data blocks tsn: 18 file#: 54 minblk 2 maxblk 2
--对照都可以对上。
总结：
1.使用系统管理表空间，位图区不仅仅在块开始的2－8块(10g)。11g没有问题，因为11g数据文件前面128块保留。
2.位图区除了位图信息，还有其他一些信息.
3.如果前面的位图区不够满足需要，从block=2的tail+1作为位图区
4.如果数据文件改变大小，如果尾部存在位图区，会出现位图区移动的情况(除非你仅仅改变减少增加几个块,这个留给大家测试)。
--补充测试：
--增加数据文件大小：
SYS@XXXXX> ALTER DATABASE DATAFILE 54 RESIZE 33554392K;
Database altered.
SYS@XXXXX> ALTER DATABASE DATAFILE 54 RESIZE 33554400K;
ALTER DATABASE DATAFILE 54 RESIZE 33554400K
*
ERROR at line 1:
ORA-03215: File Size specified for resize is too small
--我增加8k，出现一个奇怪的情况，错误ora-03215:
$ oerr ora 3215
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
-- 看见错误内容提示了吗？bitmap control structures to overlap。
ALTER DATABASE DATAFILE 54 RESIZE 33554408K;
ALTER DATABASE DATAFILE 54 RESIZE 33554416K;
ALTER DATABASE DATAFILE 54 RESIZE 33554424K;
ALTER DATABASE DATAFILE 54 RESIZE 33554432K;
ALTER DATABASE DATAFILE 54 RESIZE 33554440K;
ALTER DATABASE DATAFILE 54 RESIZE 33554448K;
--都是出现ORA-03215: File Size specified for resize is too small
SYS@XXXXX> ALTER DATABASE DATAFILE 54 RESIZE 33554456K;
ALTER DATABASE DATAFILE 54 RESIZE 33554456K
*
ERROR at line 1:
ORA-01144: File size (4194307 blocks) exceeds maximum of 4194303 blocks
--操作范围了。最大数据文件是 32*1024*1024-8 =33554424k,2^22-1=4194303块。
-- (2^22-1)*8=33554424 ,可以看出oracle先判断是否overlap,在判断是否超出最大块。
--减少数据文件大小：
ALTER DATABASE DATAFILE 54 RESIZE 33554392K;
ALTER DATABASE DATAFILE 54 RESIZE 33554384K;
--ok.
SYS@XXXXX> alter system dump datafile 54 block 2;
System altered.
Start dump data blocks tsn: 18 file#: 54 minblk 2 maxblk 2
buffer tsn: 18 rdba: 0x0d800002 (54/2)
scn: 0x0002.db197abe seq: 0x01 flg: 0x00 tail: 0x7abe1d01
frmt: 0x02 chkval: 0x0000 type: 0x1d=KTFB Bitmapped File Space Header
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0000000015459400 to 0x000000001545B400
015459400 0000A21D 0D800002 DB197ABE 00010002 [.........z......]
015459410 00000000 00000036 00000008 003FFFFA [....6.........?.]
015459420 00000001 00000000 00000000 00000007 [................]
015459430 003FFFF0 00000000 000002CD 00000000 [..?.............]
015459440 00000000 00000000 00000000 00000000 [................]
015459450 003FFFF1 00000003 00000000 00000000 [..?.............]
015459460 00000000 00000000 00000000 00000000 [................]
Repeat 504 times
01545B3F0 00000000 00000000 00000000 7ABE1D01 [...............z]
File Space Header Block:
Header Control:
RelFno: 54, Unit: 8, Size: 4194298, Flag: 1
AutoExtend: NO, Increment: 0, MaxSize: 0
Initial Area: 7, Tail: 4194288, First: 0, Free: 717
Deallocation scn: 0.0
Header Opcode:
Save: No Pending Op
End dump data blocks tsn: 18 file#: 54 minblk 2 maxblk 2
ALTER DATABASE DATAFILE 54 RESIZE 33554376K;
ALTER DATABASE DATAFILE 54 RESIZE 33554368K;
ALTER DATABASE DATAFILE 54 RESIZE 33554360K;
ALTER DATABASE DATAFILE 54 RESIZE 33554352K;
ALTER DATABASE DATAFILE 54 RESIZE 33554344K;
ALTER DATABASE DATAFILE 54 RESIZE 33554336K;
ALTER DATABASE DATAFILE 54 RESIZE 33554328K;
--都是出现ORA-03215: File Size specified for resize is too small
SYS@XXXXX> ALTER DATABASE DATAFILE 54 RESIZE 33554320K;
Database altered.
SYS@XXXXX> alter system dump datafile 54 block 2;
System altered.
*** 2015-01-12 16:47:29.914
Start dump data blocks tsn: 18 file#: 54 minblk 2 maxblk 2
buffer tsn: 18 rdba: 0x0d800002 (54/2)
scn: 0x0002.db19fc66 seq: 0x01 flg: 0x00 tail: 0xfc661d01
frmt: 0x02 chkval: 0x0000 type: 0x1d=KTFB Bitmapped File Space Header
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0000000015459400 to 0x000000001545B400
015459400 0000A21D 0D800002 DB19FC66 00010002 [........f.......]
015459410 00000000 00000036 00000008 003FFFF2 [....6.........?.]
015459420 00000001 00000000 00000000 00000007 [................]
015459430 003FFFE8 00000000 000002CC 00000000 [..?.............]
015459440 00000000 00000000 00000000 00000000 [................]
015459450 003FFFE9 00000003 00000000 00000000 [..?.............]
015459460 00000000 00000000 00000000 00000000 [................]
Repeat 504 times
01545B3F0 00000000 00000000 00000000 FC661D01 [..............f.]
File Space Header Block:
Header Control:
RelFno: 54, Unit: 8, Size: 4194290, Flag: 1
AutoExtend: NO, Increment: 0, MaxSize: 0
Initial Area: 7, Tail: 4194280, First: 0, Free: 716
Deallocation scn: 0.0
Header Opcode:
Save: No Pending Op
End dump data blocks tsn: 18 file#: 54 minblk 2 maxblk 2
--数据文件减少8*8K后，才能正常操作，从tail变化的数量也可以证明。
SYS@XXXXX> alter system flush BUFFER_CACHE;
System altered.
SYS@XXXXX> @ &r/10046on 12
Session altered.
SYS@XXXXX> select * from sys.x$ktfbfe where ktfbfefno=54;
ADDR INDX INST_ID KTFBFETSN KTFBFEFNO KTFBFEBNO KTFBFEBLKS
---------------- ------------ ------------ ------------ ------------ ------------ ------------
00002AB8C8082800 513 1 18 54 4188553 5728
SYS@XXXXX> @ &r/10046off
Session altered.
WAIT #3: nam='db file sequential read' ela= 16998 file#=54 block#=2 blocks=1 obj#=-1 tim=1387746718230540
WAIT #3: nam='db file sequential read' ela= 8186 file#=54 block#=3 blocks=1 obj#=-1 tim=1387746718238749
WAIT #3: nam='db file sequential read' ela= 272 file#=54 block#=4 blocks=1 obj#=-1 tim=1387746718239168
WAIT #3: nam='db file sequential read' ela= 10595 file#=54 block#=5 blocks=1 obj#=-1 tim=1387746718249909
WAIT #3: nam='db file sequential read' ela= 8057 file#=54 block#=6 blocks=1 obj#=-1 tim=1387746718258112
WAIT #3: nam='db file sequential read' ela= 257 file#=54 block#=7 blocks=1 obj#=-1 tim=1387746718258517
WAIT #3: nam='db file sequential read' ela= 113 file#=54 block#=8 blocks=1 obj#=-1 tim=1387746718258776
WAIT #3: nam='db file sequential read' ela= 14329 file#=54 block#=4194281 blocks=1 obj#=-1 tim=1387746718273249
WAIT #3: nam='db file sequential read' ela= 8065 file#=54 block#=4194282 blocks=1 obj#=-1 tim=1387746718281460
WAIT #3: nam='db file sequential read' ela= 258 file#=54 block#=4194283 blocks=1 obj#=-1 tim=1387746718281865
--可以发现位图区从4194281开始。
