测量uniform size 表空间中的bit map block 中的1 bit 能管理多少空间

前面一篇文章已经讨论了，在自动分配的本地管理表空间中，bit map block中的1 bit能管理多少的空间，现在测量一下在统一尺寸的本地管理的表空间中，bit map block中的1 bit 能管理多少空间。

SQL> show parameter block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------
db_block_size                        integer     8192

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

SQL> create tablespace lmt datafile 'C:/ORACLE/PRODUCT/10.2.0/ORADATA/ROBINSON/DATAFILE/lmt.dbf' size 100m
  2  extent management local uniform size 1m;

Tablespace created

SQL> create table test tablespace lmt as select * from dba_objects ;

Table created

SQL> select segment_name,tablespace_name,header_file,header_block,blocks from dba_segments where tablespace_name='LMT';

SEGMENT_NA TABLESPACE_NAME      HEADER_FILE HEADER_BLOCK     BLOCKS
---------- -------------------- ----------- ------------ ----------
TEST       LMT                            6           12        768

可以看到表test 位于LMT表空间，文件号为6，段头为12

SQL> alter system dump datafile 6 block min 1 block max 12;
系统已更改。

部分的DUMP文件

Start dump data blocks tsn: 10 file#: 6 minblk 1 maxblk 12
Block 1 (file header) not dumped: use dump file header command
buffer tsn: 10 rdba: 0x01800002 (6/2)
scn: 0x0000.001d400c seq: 0x02 flg: 0x04 tail: 0x400c1d02
frmt: 0x02 chkval: 0xbbd8 type: 0x1d=KTFB Bitmapped File Space Header
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x08127800 to 0x08129800
8127800 0000A21D 01800002 001D400C 04020000  [.........@......]
8127810 0000BBD8 00000006 00000080 00003200  [.............2..]
8127820 00000001 00000000 00000000 00000007  [................]
8127830 00003188 00000006 0000005D 00000000  [.1......].......]
8127840 00000000 00000000 00000000 00000000  [................]
8127850 00000289 00000080 00000000 00000000  [................]
8127860 00000000 00000000 00000000 00000000  [................]
        Repeat 504 times
81297F0 00000000 00000000 00000000 400C1D02  [...............@]
File Space Header Block:
Header Control:
RelFno: 6, Unit: 128, Size: 12800, Flag: 1
AutoExtend: NO, Increment: 0, MaxSize: 0
Initial Area: 7, Tail: 12680, First: 6, Free: 93
Deallocation scn: 0.0
Header Opcode:
Save: No Pending Op
buffer tsn: 10 rdba: 0x01800003 (6/3)
scn: 0x0000.001d400c seq: 0x01 flg: 0x04 tail: 0x400c1e01
frmt: 0x02 chkval: 0x4e4c type: 0x1e=KTFB Bitmapped File Space Bitmap
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x08127800 to 0x08129800
8127800 0000A21E 01800003 001D400C 04010000  [.........@......]
8127810 00004E4C 00000006 00000009 00000000  [LN..............]
8127820 00000006 0000F7FA 00000000 00000000  [................]
8127830 00000000 00000000 0000003F 00000000  [........?.......]
8127840 00000000 00000000 00000000 00000000  [................]
        Repeat 506 times
81297F0 00000000 00000000 00000000 400C1E01  [...............@]
File Space Bitmap Block:
BitMap Control:
RelFno: 6, BeginBlock: 9, Flag: 0, First: 6, Free: 63482   -----用了6位来管理空间
3F00000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000

.................................省略若干............................

buffer tsn: 10 rdba: 0x01800009 (6/9)         -----注意，ORACLE同样预留了6个block来管理空闲空间，有2个一级位图块9，10
scn: 0x0000.001d400f seq: 0x03 flg: 0x04 tail: 0x400f2003
frmt: 0x02 chkval: 0x4a4a type: 0x20=FIRST LEVEL BITMAP BLOCK
注意，这里有2个一级位图块，一个为9，一个为10，11的为二级位图块，12的为段头。

可以看到First=6,那么猜想一下，这个六代表什么呢？

SQL> select count(*) from dba_extents where tablespace_name='LMT';

  COUNT(*)
----------
         6

区间数也等于6，因此我猜1 bit 表示一个extent，继续实验

SQL> insert into test  select * from dba_objects nologging;

49962 rows inserted
SQL> commit;

Commit complete

SQL> select count(*) from dba_extents where tablespace_name='LMT';

  COUNT(*)
----------
        11

这里区间数增加到了11个了

SQL> alter system dump datafile 6 block 3;

系统已更改。

DUMP文件主要内容：

Start dump data blocks tsn: 10 file#: 6 minblk 3 maxblk 3
buffer tsn: 10 rdba: 0x01800003 (6/3)
scn: 0x0000.001d42f6 seq: 0x01 flg: 0x00 tail: 0x42f61e01
frmt: 0x02 chkval: 0x0000 type: 0x1e=KTFB Bitmapped File Space Bitmap
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x04807800 to 0x04809800
4807800 0000A21E 01800003 001D42F6 00010000  [.........B......]
4807810 00000000 00000006 00000009 00000000  [................]
4807820 0000000B 0000F7F5 00000000 00000000  [................]
4807830 00000000 00000000 000007FF 00000000  [................]
4807840 00000000 00000000 00000000 00000000  [................]
        Repeat 506 times
48097F0 00000000 00000000 00000000 42F61E01  [...............B]
File Space Bitmap Block:
BitMap Control:
RelFno: 6, BeginBlock: 9, Flag: 0, First: 11, Free: 63477
FF07000000000000 0000000000000000 0000000000000000 0000000000000000

First=11表示有11个bit，

因此可以说在统一尺寸的本地管理表空间下，bit map block中的1 bit 代表一个extent,所以1 bit 能管理多少空间，在于你的设置。