关于位图数据位和系统管理区大小-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 
...省略...

正如预测那样子。