oracle表预留的3个BLOCK

hxaf6odd8t

oracle表的segment的结构是：
1、FIRST LEVEL BITMAP BLOCK
2、SECOND LEVEL BITMAP BLOCK
3、PAGETABLE SEGMENT HEADER
4、trans data
用户数据实际上是插在DATA BLCOK中去的，
所以，查看表中行数据占据的blockID：

select id,dbms_rowid.rowid_relative_fno(rowid) file#,
dbms_rowid.rowid_block_number(rowid) block# from t2;

结果如下：

id     file#   block#
2460
5460
5460
5460
5460
5460

这里可以看书表T2所有的行都分布在block 60中
而查看表总共占据的block数据：

exec dbms_stats.gather_table_stats(user,'T2');
select   NUM_ROWS,BLOCKS from user_tables where table_name='T2';

结果如下：

NUM_ROWS BLOCKS
64

结果看出，表实际占用的blocks是比行数据占据的block多了3个，
这三个block就。
1、FIRST LEVEL BITMAP BLOCK
2、SECOND LEVEL BITMAP BLOCK
3、PAGETABLE SEGMENT HEADER
通过以下SQL查看表开始block位置：

select BLOCK_ID,blocks  from dba_extents  where segment_name ='T2'

结果:

BLOCK_ID,blocks
57    8


将57-60的blockdump出来：

alter system dump datafile 4 block 57
alter system dump datafile 4 block 58
alter system dump datafile 4 block 59
alter system dump datafile 4 block 60


结果如下：
Dump file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_s000_2584.trc
Sun Jul 15 10:58:10 2012
ORACLE V10.2.0.1.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Windows NT Version V6.1 Service Pack 1
CPU                 : 4 - type 586, 2 Physical Cores
Process Affinity    : 0x00000000
Memory (Avail/Total): Ph:1293M/3241M, Ph+PgF:3060M/6480M, VA:1244M/2047M
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 16
Windows thread id: 2584, image: ORACLE.EXE (S000)

*** 2012-07-15 10:58:11.104
*** SERVICE NAME:(SYS$USERS) 2012-07-15 10:58:10.955
*** SESSION ID:(143.9) 2012-07-15 10:58:10.955
Start dump data blocks tsn: 4 file#: 4 minblk 57 maxblk 57
buffer tsn: 4 rdba: 0x01000039 (4/57)
scn: 0x0000.002d8b2c seq: 0x02 flg: 0x04 tail: 0x8b2c2002
frmt: 0x02 chkval: 0x47d7 type: 0x20=FIRST LEVEL BITMAP BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0A2D8400 to 0x0A2DA400
A2D8400 0000A220 01000039 002D8B2C 04020000  [ ...9...,.-.....]
A2D8410 000047D7 00000000 00000000 00000000  [.G..............]
A2D8420 00000000 00000000 00000000 00000000  [................]
        Repeat 1 times
A2D8440 00000000 00000000 00000000 00000004  [................]
A2D8450 FFFFFFFF 00000004 00000003 00000008  [................]
A2D8460 00010001 00000000 00000000 00000000  [................]
A2D8470 00000000 00000004 00000000 00000000  [................]
A2D8480 00000000 00000000 00000000 00000000  [................]
A2D8490 0100003A 00000000 00000000 00000004  [:...............]
A2D84A0 00000008 0100003D 00000000 00000000  [....=...........]
A2D84B0 00000000 00000004 00000000 00000001  [................]
A2D84C0 0000D0C2 00000000 00000000 01000039  [............9...]
A2D84D0 00000008 00000000 00000000 00000000  [................]
A2D84E0 00000000 00000000 00000000 00000000  [................]
        Repeat 9 times
A2D8580 00000000 00000000 00000000 00001111  [................]
A2D8590 00000000 00000000 00000000 00000000  [................]
        Repeat 485 times
A2DA3F0 00000000 00000000 00000000 8B2C2002  [............. ,.]
Dump of First Level Bitmap Block
--------------------------------
   nbits : 4 nranges: 1         parent dba:  0x0100003a   poffset: 0     
   unformatted: 4       total: 8         first useful block: 3      
   owning instance : 1
   instance ownership changed at
   Last successful Search
   Freeness Status:  nf1 0      nf2 0      nf3 0      nf4 0      
   Extent Map Block Offset: 4294967295
   First free datablock : 4      
   Bitmap block lock opcode 0
   Locker xid:     :  0x0000.000.00000000
   Inc #: 0 Objd: 53442
  HWM Flag: HWM Set
      Highwater::  0x0100003d  ext#: 0      blk#: 4      ext size: 8     
  #blocks in seg. hdr's freelists: 0     
  #blocks below: 4     
  mapblk  0x00000000  offset: 0     
  --------------------------------------------------------
  DBA Ranges :
  --------------------------------------------------------
   0x01000039  Length: 8      Offset: 0      
   0:Metadata   1:Metadata   2:Metadata   3:FULL
   4:unformatted   5:unformatted   6:unformatted   7:unformatted
  --------------------------------------------------------
End dump data blocks tsn: 4 file#: 4 minblk 57 maxblk 57
*** 2012-07-15 11:04:53.535
Start dump data blocks tsn: 4 file#: 4 minblk 58 maxblk 58
buffer tsn: 4 rdba: 0x0100003a (4/58)
scn: 0x0000.002d8b28 seq: 0x01 flg: 0x04 tail: 0x8b282101
frmt: 0x02 chkval: 0x56f3 type: 0x21=SECOND LEVEL BITMAP BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0A2D8400 to 0x0A2DA400
A2D8400 0000A221 0100003A 002D8B28 04010000  [!...:...(.-.....]
A2D8410 000056F3 00000000 00000000 00000000  [.V..............]
A2D8420 00000000 00000000 00000000 00000000  [................]
        Repeat 1 times
A2D8440 00000000 00000000 00000000 0100003B  [............;...]
A2D8450 00000001 00000001 00000000 00000000  [................]
A2D8460 00000000 00000000 0000D0C2 00000001  [................]
A2D8470 00000000 01000039 00010005 00000000  [....9...........]
A2D8480 00000000 00000000 00000000 00000000  [................]
        Repeat 502 times
A2DA3F0 00000000 00000000 00000000 8B282101  [.............!(.]
Dump of Second Level Bitmap Block
   number: 1       nfree: 1       ffree: 0      pdba:     0x0100003b
   Inc #: 0 Objd: 53442
  opcode:0
xid:
  L1 Ranges :
  --------------------------------------------------------
   0x01000039  Free: 5 Inst: 1
  --------------------------------------------------------
End dump data blocks tsn: 4 file#: 4 minblk 58 maxblk 58
*** 2012-07-15 11:05:22.720
Start dump data blocks tsn: 4 file#: 4 minblk 59 maxblk 59
buffer tsn: 4 rdba: 0x0100003b (4/59)
scn: 0x0000.002d8b3c seq: 0x01 flg: 0x04 tail: 0x8b3c2301
frmt: 0x02 chkval: 0x7b60 type: 0x23=PAGETABLE SEGMENT HEADER
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0A2D8400 to 0x0A2DA400
A2D8400 0000A223 0100003B 002D8B3C 04010000  [#...;...<.-.....]
A2D8410 00007B60 00000000 00000000 00000000  [`{..............]
A2D8420 00000000 00000001 00000008 00000A9C  [................]
A2D8430 00000000 00000004 00000008 0100003D  [............=...]
A2D8440 00000000 00000000 00000000 00000004  [................]
A2D8450 00000000 00000000 00000000 00000000  [................]
A2D8460 00000004 00000008 0100003D 00000000  [........=.......]
A2D8470 00000000 00000000 00000004 01000039  [............9...]
A2D8480 01000039 00000000 00000000 00000000  [9...............]
A2D8490 00000000 00000000 00000000 00000000  [................]
        Repeat 3 times
A2D84D0 00000001 00002000 00000000 00001434  [..... ......4...]
A2D84E0 00000000 0100003A 00000001 01000039  [....:.......9...]
A2D84F0 0100003A 00000000 00000000 00000000  [:...............]
A2D8500 00000000 00000000 00000001 00000000  [................]
A2D8510 0000D0C2 10000000 01000039 00000008  [........9.......]
A2D8520 00000000 00000000 00000000 00000000  [................]
        Repeat 152 times
A2D8EB0 01000039 0100003C 00000000 00000000  [9...<...........]
A2D8EC0 00000000 00000000 00000000 00000000  [................]
        Repeat 151 times
A2D9840 00000000 00000000 0100003A 00000000  [........:.......]
A2D9850 00000000 00000000 00000000 00000000  [................]
        Repeat 185 times
A2DA3F0 00000000 00000000 00000000 8B3C2301  [.............#<.]
  Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 1      #blocks: 8     
                  last map  0x00000000  #maps: 0      offset: 2716  
      Highwater::  0x0100003d  ext#: 0      blk#: 4      ext size: 8     
  #blocks in seg. hdr's freelists: 0     
  #blocks below: 4     
  mapblk  0x00000000  offset: 0     
                   Unlocked
  --------------------------------------------------------
  Low HighWater Mark :
      Highwater::  0x0100003d  ext#: 0      blk#: 4      ext size: 8     
  #blocks in seg. hdr's freelists: 0     
  #blocks below: 4     
  mapblk  0x00000000  offset: 0     
  Level 1 BMB for High HWM block: 0x01000039
  Level 1 BMB for Low HWM block: 0x01000039
  --------------------------------------------------------
  Segment Type: 1 nl2: 1      blksz: 8192   fbsz: 0      
  L2 Array start offset:  0x00001434
  First Level 3 BMB:  0x00000000
  L2 Hint for inserts:  0x0100003a
  Last Level 1 BMB:  0x01000039
  Last Level II BMB:  0x0100003a
  Last Level III BMB:  0x00000000
     Map Header:: next  0x00000000  #extents: 1    obj#: 53442  flag: 0x10000000
  Inc # 0
  Extent Map
  -----------------------------------------------------------------
   0x01000039  length: 8     
  Auxillary Map
  --------------------------------------------------------
   Extent 0     :  L1 dba:  0x01000039 Data dba:  0x0100003c
  --------------------------------------------------------
   Second Level Bitmap block DBAs
   --------------------------------------------------------
   DBA 1:   0x0100003a
End dump data blocks tsn: 4 file#: 4 minblk 59 maxblk 59
Start dump data blocks tsn: 4 file#: 4 minblk 60 maxblk 60
buffer tsn: 4 rdba: 0x0100003c (4/60)
scn: 0x0000.002d8b3c seq: 0x01 flg: 0x04 tail: 0x8b3c0601
frmt: 0x02 chkval: 0xfed8 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0A2D8400 to 0x0A2DA400
A2D8400 0000A206 0100003C 002D8B3C 04010000  [....<...<.-.....]
A2D8410 0000FED8 00000001 0000D0C2 002D8B3C  [............<.-.]
A2D8420 00000000 00320003 01000039 0003000A  [......2.9.......]
A2D8430 00000264 00000000 00000000 00008000  [d...............]
A2D8440 002D8B31 00000000 00000000 00000000  [1.-.............]
A2D8450 00000000 00000000 00000000 00000000  [................]
        Repeat 1 times
A2D8470 00000000 00000000 00000000 00060100  [................]
A2D8480 001EFFFF 1F2C1F4A 00001F2C 1F7A0006  [....J.,.,.....z.]
A2D8490 1F681F71 1F561F5F 00001F4D 00000000  [q.h._.V.M.......]
A2D84A0 00000000 00000000 00000000 00000000  [................]
        Repeat 497 times
A2DA3C0 00000000 00000000 01002C00 0006C102  [.........,......]
A2DA3D0 002C0000 06C10201 2C000000 C1020100  [..,........,....]
A2DA3E0 00000006 0201002C 000006C1 01002C00  [....,........,..]
A2DA3F0 0006C102 002C0000 03C10201 8B3C0601  [......,.......<.]
Block header dump:  0x0100003c
Object id on Block? Y
seg/obj: 0xd0c2  csc: 0x00.2d8b3c  itc: 3  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1000039 ver: 0x01 opc: 0
     inc: 0  exflg: 0
Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000a.003.00000264  0x00000000.0000.00  C---    0  scn 0x0000.002d8b31
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x03   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
data_block_dump,data header at 0xa2d847c
===============
tsiz: 0x1f80
hsiz: 0x1e
pbl: 0x0a2d847c
bdba: 0x0100003c
     76543210
flag=--------
ntab=1
nrow=6
frre=-1
fsbo=0x1e
fseo=0x1f4a
avsp=0x1f2c
tosp=0x1f2c
0xe:pti[0]nrow=6offs=0
0x12:pri[0]offs=0x1f7a
0x14:pri[1]offs=0x1f71
0x16:pri[2]offs=0x1f68
0x18:pri[3]offs=0x1f5f
0x1a:pri[4]offs=0x1f56
0x1c:pri[5]offs=0x1f4d
block_row_dump:
tab 0, row 0, @0x1f7a
tl: 6 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 2]  c1 03
tab 0, row 1, @0x1f71
tl: 6 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 2]  c1 06
tab 0, row 2, @0x1f68
tl: 6 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 2]  c1 06
tab 0, row 3, @0x1f5f
tl: 6 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 2]  c1 06
tab 0, row 4, @0x1f56
tl: 6 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 2]  c1 06
tab 0, row 5, @0x1f4d
tl: 6 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 2]  c1 06
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 60 maxblk 60
三个BLOCK之间的关系：
在本地管理表空间中ORACLE自动管理分配给段的区的大小，只在本地管理的表空间中才能选用段自动管理，采用自动段空间管理的本地管理表空间中的段中的空闲数据块的信息就存放在段中某些区的头部，使用位图来管理（最普通的情况是一个段的第一个区的第一个块为FIRST LEVEL BITMAP BLOCK，第二个块为SECOND LEVEL BITMAP BLOCK，第三个块为PAGETABLE SEGMENT HEADER，再下面的块为记录数据的数据块，FIRST LEVEL BITMAP BLOCK的父数据块地址指向SECOND LEVEL BITMAP BLOCK，SECOND LEVEL BITMAP BLOCK的父数据块地址指向PAGETABLE SEGMENT HEADER，FIRST LEVEL BITMAP BLOCK记录了它所管理的所有块（包括头部三个块，不仅仅指数据块）的状态，标识的状态有Metadata、75-100% free、50-75% free、25-50% free、0-25% free、full、unformatted，在SECOND LEVEL BITMAP BLOCK中有一个列表，记录了它管理的FIRST LEVEL BITMAP BLOCK，PAGETABLE SEGMENT HEADER中记录的内容比较多，除了记录了它管理的SECOND LEVEL BITMAP BLOCK，还记录了各个区的首块地址以及各个区的DB BLOCK的个数，段的各个区所对应的FIRST LEVEL BITMAP BLOCK的块地址以及区里面记录数据的数据块的起始地址。如果一个区拥有很多块，这时会在一个区里出现两个或多个FIRST LEVEL BITMAP BLOCK，这些FIRST LEVEL BITMAP BLOCK分别管理一个区中的一些块，当区的数据块比较少时，一个区的FIRST LEVEL BITMAP BLOCK可以跨区管理多个区的数据块，BITMAP BOLCK最多为三级）。