[20150113]系统管理表空间的疑问2.txt
--昨天探究系统管理表空间位图区分布的问题。
--自己得到一些结论：
http://blog.itpub.net/267265/viewspace-1399275/
总结：
1.使用系统管理表空间，位图区不仅仅在块开始的2－8块(10g)。11g没有问题，因为11g数据文件前面128块保留。
2.位图区除了位图信息，还有其他一些信息.
3.如果前面的位图区不够满足需要，从block=2的tail+1作为位图区
4.如果数据文件改变大小，如果尾部存在位图区，会出现位图区移动的情况(除非你仅仅改变减少增加几个块,这个留给大家测试)。
5.改变减少增加几个块，可能出现位图重叠的问题，出现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
--我当时还得到一个结论，如果设置
文件大小改变：
ALTER DATABASE DATAFILE 54 RESIZE 33554392K;
--这样后面的3个块正好够放余下的位图区，需要3块，位于块4194297，4194298，4194299。
--当前情况如下：
ALTER DATABASE DATAFILE 54 RESIZE 33554320K;
alter system dump datafile 54 block 2;
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: 0x04 tail: 0xfc661d01
frmt: 0x02 chkval: 0x90bf type: 0x1d=KTFB Bitmapped File Space Header
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x000000001B0F3400 to 0x000000001B0F5400
01B0F3400 0000A21D 0D800002 DB19FC66 04010002 [........f.......]
01B0F3410 000090BF 00000036 00000008 003FFFF2 [....6.........?.]
01B0F3420 00000001 00000000 00000000 00000007 [................]
01B0F3430 003FFFE8 00000000 000002CC 00000000 [..?.............]
01B0F3440 00000000 00000000 00000000 00000000 [................]
01B0F3450 003FFFE9 00000003 00000000 00000000 [..?.............]
01B0F3460 00000000 00000000 00000000 00000000 [................]
Repeat 504 times
01B0F53F0 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
--我们可以看到位图区位于4194281,4194282,4194283.
--如果我修改33554392 -8 =33554384k,这样结尾仅仅剩余2块，是无法容纳位图区，看看oracle是如何操作的?
SYS@xxxxx> ALTER DATABASE DATAFILE 54 RESIZE 33554384K;
Database altered.
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.db459295 seq: 0x01 flg: 0x00 tail: 0x92951d01
frmt: 0x02 chkval: 0x0000 type: 0x1d=KTFB Bitmapped File Space Header
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x000000001B31C400 to 0x000000001B31E400
01B31C400 0000A21D 0D800002 DB459295 00010002 [..........E.....]
01B31C410 00000000 00000036 00000008 003FFFFA [....6.........?.]
01B31C420 00000001 00000000 00000000 00000007 [................]
01B31C430 003FFFF0 00000000 000002CD 00000000 [..?.............]
01B31C440 00000000 00000000 00000000 A9010000 [................]
01B31C450 003FFFF1 00000003 00000000 00000000 [..?.............]
01B31C460 00000000 00000000 00000000 00000000 [................]
Repeat 504 times
01B31E3F0 00000000 00000000 00000000 92951D01 [................]
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
--可以发现位图区发生了改变，但是并没有移动到最后，tail:4194288。位图区4194289，4194290，4194291
SYS@xxxxx> ALTER DATABASE DATAFILE 54 RESIZE 33554392K;
Database altered.
SYS@xxxxx> alter system dump datafile 54 block 2;
System altered.
*** 2015-01-13 10:04:34.379
Start dump data blocks tsn: 18 file#: 54 minblk 2 maxblk 2
buffer tsn: 18 rdba: 0x0d800002 (54/2)
scn: 0x0002.db45dfc4 seq: 0x01 flg: 0x00 tail: 0xdfc41d01
frmt: 0x02 chkval: 0x0000 type: 0x1d=KTFB Bitmapped File Space Header
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x000000001B31C400 to 0x000000001B31E400
01B31C400 0000A21D 0D800002 DB45DFC4 00010002 [..........E.....]
01B31C410 00000000 00000036 00000008 003FFFFB [....6.........?.]
01B31C420 00000001 00000000 00000000 00000007 [................]
01B31C430 003FFFF8 00000000 000002CE 00000000 [..?.............]
01B31C440 00000000 00000000 00000000 A9010000 [................]
01B31C450 003FFFF9 00000003 00000000 00000000 [..?.............]
01B31C460 00000000 00000000 00000000 00000000 [................]
Repeat 504 times
01B31E3F0 00000000 00000000 00000000 DFC41D01 [................]
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: 0, Free: 718
Deallocation scn: 0.0
Header Opcode:
Save: No Pending Op
End dump data blocks tsn: 18 file#: 54 minblk 2 maxblk 2
--仅仅数据文件增加8k，这样结尾正好能非放下3块。位图区4194297，4194298，4194299.从视图也可以确定。
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
--BLOCKS =4194299.
--也就是数据文件大小改变时结尾要保留放下位图区的空间，后面的空间就浪费了。
--减少8k时：
SYS@xxxxx> ALTER DATABASE DATAFILE 54 RESIZE 33554384K;
Database altered.
SYS@xxxxx> alter system dump datafile 54 block 2;
System altered.
*** 2015-01-13 10:12:31.841
Start dump data blocks tsn: 18 file#: 54 minblk 2 maxblk 2
buffer tsn: 18 rdba: 0x0d800002 (54/2)
scn: 0x0002.db46da1f seq: 0x01 flg: 0x00 tail: 0xda1f1d01
frmt: 0x02 chkval: 0x0000 type: 0x1d=KTFB Bitmapped File Space Header
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x000000001B31C400 to 0x000000001B31E400
01B31C400 0000A21D 0D800002 DB46DA1F 00010002 [..........F.....]
01B31C410 00000000 00000036 00000008 003FFFFA [....6.........?.]
01B31C420 00000001 00000000 00000000 00000007 [................]
01B31C430 003FFFF0 00000000 000002CD 00000000 [..?.............]
01B31C440 00000000 00000000 00000000 00000000 [................]
01B31C450 003FFFF1 00000003 00000000 00000000 [..?.............]
01B31C460 00000000 00000000 00000000 00000000 [................]
Repeat 504 times
01B31E3F0 00000000 00000000 00000000 DA1F1D01 [................]
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
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 34359689216 4194298 AVAILABLE 54 NO 0 0 0 34359541760 4194280 ONLINE
--BLOCKS=4194298.位图区4194289，4194290，4194291, 后面的4194292－4194298就浪费了，当然这点空间根本不算什么。
----对于asm就不算什么，因为aus＝1M。