关于位图数据位和系统管理区大小-P6
1 背景
Vage大师的一段话。
还有一个问题，不知道大家有没考虑到。这个问题涉及统一区大小表空间的位图块。每个二进制位对应一个区的使用情况，这是没问题的，但系统管理区大小呢？就比如刚才创建的TABLE_LHB1表，前16个区大小为64KB，之后的区大小为1MB。区的大小不同，如何用二进制位来反映区的使用情况呢？
Oracle的处理方法是这样的，以64KB（也就是8个块）为准，每个二进制位对应64KB。1MB的区，对应16个二进制位。每分配一个1MB的区，Oracle将对应的16个二进制位（也就是两个字节）设置为1。释放一个区也同样，将16个二进制位设置为0。这样就解决了区大小不统一的问题，Oracle的解决方法还是很巧妙的！
2 验证
2.1 环境信息
SYS@zkm1> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
2.2 创建表空间tbs1
SYS@zkm1> drop tablespace tbs1 including contents and datafiles;
Tablespace dropped.
SYS@zkm1> create tablespace tbs1 datafile '+arch' size 1g autoextend on;
Tablespace created.
2.3 创建表段并拓展至16个区
SYS@zkm1> create table zkm.test as select rownum id from dual where rownum=1;
Table created.
SYS@zkm1> select SEGMENT_NAME,EXTENT_ID,FILE_ID,BLOCK_ID,BYTES,BLOCKS from dba_extents where owner='ZKM' and SEGMENT_NAME like 'TEST%';
SEGMENT_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS
--------------- ---------- ---------- ---------- ---------- ----------
TEST 0 6 128 65536 8
SYS@zkm1> alter table zkm.test allocate extent (size 960k);
Table altered.
SYS@zkm1> select SEGMENT_NAME,EXTENT_ID,FILE_ID,BLOCK_ID,BYTES,BLOCKS from dba_extents where owner='ZKM' and SEGMENT_NAME like 'TEST%';
SEGMENT_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS
--------------- ---------- ---------- ---------- ---------- ----------
TEST 0 6 128 65536 8
TEST 1 6 136 65536 8
TEST 2 6 144 65536 8
TEST 3 6 152 65536 8
TEST 4 6 160 65536 8
TEST 5 6 168 65536 8
TEST 6 6 176 65536 8
TEST 7 6 184 65536 8
TEST 8 6 192 65536 8
TEST 9 6 200 65536 8
TEST 10 6 208 65536 8
SEGMENT_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS
--------------- ---------- ---------- ---------- ---------- ----------
TEST 11 6 216 65536 8
TEST 12 6 224 65536 8
TEST 13 6 232 65536 8
TEST 14 6 240 65536 8
TEST 15 6 248 65536 8
16 rows selected.
2.4 查看3号位图块信息
SYS@zkm1> select value from v$diag_info where name like 'De%';
VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/zkm/zkm1/trace/zkm1_ora_128094.trc
SYS@zkm1> exec dbms_space_admin.tablespace_dump_bitmaps('TBS1');
PL/SQL procedure successfully completed.
/////zkm1_ora_128094.trc文件信息
Header Control:
RelFno: 6, Unit: 8, Size: 131072, Flag: 9
AutoExtend: YES, Increment: 12800, MaxSize: 4194302
Initial Area: 126, Tail: 131071, First: 16, Free: 16352
Deallocation scn: 3.0
Header Opcode:
Save: No Pending Op
File Space Bitmap Block:
BitMap Control:
RelFno: 6, BeginBlock: 128, Flag: 0, First: 16, Free: 63472
FFFF000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
...省略...
其中，FFFF即为1111 1111 1111 1111，刚好16个区。
接下去16号区（第17个），区有128个块，大小刚好为1M。按照Vage的说法，每个二进制位对应64KB（0-15号区每个区大小均为64K，所以16个二进制位即可），那么第16号区（第17个）大小位1M，需要1M*1024/64KB=16个二进制位。
那样，预计拓展到16号区（第17个）之后，会出现FFFFFFFF。
2.5 拓展16号区
SYS@zkm1> alter table zkm.test allocate extent (size 1k);
Table altered.
SYS@zkm1> select SEGMENT_NAME,EXTENT_ID,FILE_ID,BLOCK_ID,BYTES,BLOCKS from dba_extents where owner='ZKM' and SEGMENT_NAME like 'TEST%';
SEGMENT_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS
--------------- ---------- ---------- ---------- ---------- ----------
TEST 0 6 128 65536 8
TEST 1 6 136 65536 8
TEST 2 6 144 65536 8
TEST 3 6 152 65536 8
TEST 4 6 160 65536 8
TEST 5 6 168 65536 8
TEST 6 6 176 65536 8
TEST 7 6 184 65536 8
TEST 8 6 192 65536 8
TEST 9 6 200 65536 8
TEST 10 6 208 65536 8
SEGMENT_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS
--------------- ---------- ---------- ---------- ---------- ----------
TEST 11 6 216 65536 8
TEST 12 6 224 65536 8
TEST 13 6 232 65536 8
TEST 14 6 240 65536 8
TEST 15 6 248 65536 8
TEST 16 6 256 1048576 128
17 rows selected.
2.6 查看3号位图块信息
SYS@zkm1> select value from v$diag_info where name like 'De%';
VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/zkm/zkm1/trace/zkm1_ora_362.trc
SYS@zkm1> exec dbms_space_admin.tablespace_dump_bitmaps('TBS1');
PL/SQL procedure successfully completed.
/////zkm1_ora_362.trc文件信息
Header Control:
RelFno: 6, Unit: 8, Size: 131072, Flag: 9
AutoExtend: YES, Increment: 12800, MaxSize: 4194302
Initial Area: 126, Tail: 131071, First: 32, Free: 16336
Deallocation scn: 3.0
Header Opcode:
Save: No Pending Op
File Space Bitmap Block:
BitMap Control:
RelFno: 6, BeginBlock: 128, Flag: 0, First: 32, Free: 63456
FFFFFFFF00000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
...省略...
正如预测那样子。
