关于ASSM的段头

什么是ASSM的段头？这里是指ASSM里的一个表在刚建立的时候就有的，也就是其初始metadata所在的block。有人曾经提到说ASSM的段头是有3个block，其实这是不准确的，这里也有可能是4个block。dba_segments里的HEADER_FILE和HEADER_BLOCK在ASSM里指的就是PAGETABLE SEGMENT HEADER。

我们来看两个例子，例一中段头有3个block，例二中段头有4个block。

例一：

SQL> create tablespace testtbs datafile '/iprat02/ipratest/testtbs_01.dbf' size 1M;

Tablespace created

SQL> select EXTENT_MANAGEMENT,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces where tablespace_name='TESTTBS1';

EXTENT_MANAGEMENT SEGMENT_SPACE_MANAGEMENT

----------------- ------------------------

LOCAL AUTO

SQL> create table t1 (id number, name varchar2(30)) tablespace testtbs storage (initial 896K);

Table created

SQL> create table t2 (id number, c1 char(2000), c2 char(2000), c3 char(2000)) tablespace testtbs storage (initial 64K);

Table created

SQL> insert into t2 VALUES (1, 'A', 'A', 'A');

1 row inserted

SQL> insert into t2 VALUES (2, 'A', 'A', 'A');

1 row inserted

SQL> insert into t2 VALUES (3, 'A', 'A', 'A');

1 row inserted

SQL> insert into t2 VALUES (4, 'A', 'A', 'A');

1 row inserted

SQL> insert into t2 VALUES (5, 'A', 'A', 'A');

1 row inserted

SQL> commit;

Commit complete

SQL> insert into t2 VALUES (6, 'A', 'A', 'A');

insert into t2 VALUES (6, 'A', 'A', 'A')

ORA-01653: unable to extend table SYS.T2 by 8 in tablespace TESTTBS

SQL> select file_id,block_id from dba_extents where segment_name='T2';

FILE_ID BLOCK_ID

---------- ----------

19 121

SQL> select header_file,header_block from dba_segments where segment_name='T2';

HEADER_FILE HEADER_BLOCK

----------- ------------

19 123

我们分别dump 19-121、19-122、19-123这三个block：

19-121的内容：

Start dump data blocks tsn: 12 file#: 19 minblk 121 maxblk 121

buffer tsn: 12 rdba: 0x04c00079 (19/121)

scn: 0x0009.1523a924 seq: 0x07 flg: 0x04 tail: 0xa9242007

frmt: 0x02 chkval: 0x31b2 type: 0x20=FIRST LEVEL BITMAP BLOCK

......中间内容省略

Dump of First Level Bitmap Block

--------------------------------

nbits : 4 nranges: 1 parent dba: 0x04c0007a poffset: 0

unformatted: 0 total: 8 first useful block: 3

owning instance : 1

instance ownership changed at 04/21/2009 15:30:35

Last successful Search 04/21/2009 15:30:35

Freeness Status: nf1 0 nf2 5 nf3 0 nf4 0

Extent Map Block Offset: 4294967295

First free datablock : 3

Bitmap block lock opcode 0

Locker xid: : 0x0000.000.00000000

Inc #: 0 Objd: 66787

HWM Flag: HWM Set

Highwater:: 0x04c00081 ext#: 0 blk#: 8 ext size: 8

#blocks in seg. hdr's freelists: 0

#blocks below: 5

mapblk 0x00000000 offset: 0

--------------------------------------------------------

DBA Ranges :

--------------------------------------------------------

0x04c00079 Length: 8 Offset: 0

0:Metadata 1:Metadata 2:Metadata 3:25-50% free

4:25-50% free 5:25-50% free 6:25-50% free 7:25-50% free

--------------------------------------------------------

End dump data blocks tsn: 12 file#: 19 minblk 121 maxblk 121

19-122的内容：

Start dump data blocks tsn: 12 file#: 19 minblk 122 maxblk 122

buffer tsn: 12 rdba: 0x04c0007a (19/122)

scn: 0x0009.1523a924 seq: 0x01 flg: 0x04 tail: 0xa9242101

frmt: 0x02 chkval: 0x17d7 type: 0x21=SECOND LEVEL BITMAP BLOCK

Hex dump of block: st=0, typ_found=1

......中间内容省略

Dump of Second Level Bitmap Block

number: 1 nfree: 1 ffree: 0 pdba: 0x04c0007b

Inc #: 0 Objd: 66787

opcode:0

xid:

L1 Ranges :

--------------------------------------------------------

0x04c00079 Free: 3 Inst: 1

--------------------------------------------------------

End dump data blocks tsn: 12 file#: 19 minblk 122 maxblk 122

19-123的内容：

Start dump data blocks tsn: 12 file#: 19 minblk 123 maxblk 123

buffer tsn: 12 rdba: 0x04c0007b (19/123)

scn: 0x0009.1523a981 seq: 0x01 flg: 0x04 tail: 0xa9812301

frmt: 0x02 chkval: 0x3ec3 type: 0x23=PAGETABLE SEGMENT HEADER

Hex dump of block: st=0, typ_found=1

......中间内容省略

Extent Control Header

-----------------------------------------------------------------

Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 8

last map 0x00000000 #maps: 0 offset: 2716

Highwater:: 0x04c00081 ext#: 0 blk#: 8 ext size: 8

#blocks in seg. hdr's freelists: 0

#blocks below: 5

mapblk 0x00000000 offset: 0

Unlocked

--------------------------------------------------------

Low HighWater Mark :

Highwater:: 0x04c00081 ext#: 0 blk#: 8 ext size: 8

#blocks in seg. hdr's freelists: 0

#blocks below: 5

mapblk 0x00000000 offset: 0

Level 1 BMB for High HWM block: 0x04c00079

Level 1 BMB for Low HWM block: 0x04c00079

--------------------------------------------------------

Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0

L2 Array start offset: 0x00001434

First Level 3 BMB: 0x00000000

L2 Hint for inserts: 0x04c0007a

Last Level 1 BMB: 0x04c00079

Last Level II BMB: 0x04c0007a

Last Level III BMB: 0x00000000

Map Header:: next 0x00000000 #extents: 1 obj#: 66787 flag: 0x10000000

Inc # 0

Extent Map

-----------------------------------------------------------------

0x04c00079 length: 8

Auxillary Map

--------------------------------------------------------

Extent 0 : L1 dba: 0x04c00079 Data dba: 0x04c0007c

--------------------------------------------------------

Second Level Bitmap block DBAs

--------------------------------------------------------

DBA 1: 0x04c0007a

End dump data blocks tsn: 12 file#: 19 minblk 123 maxblk 123

从结果里可以看到对于表T2，它的段头是有3个block。这个从19-121的dump内容中可以很清晰的看出来，里面有如下的内容：

first useful block: 3

0:Metadata 1:Metadata 2:Metadata 3:25-50% free

4:25-50% free 5:25-50% free 6:25-50% free 7:25-50% free

例二：

SQL> select tablespace_name from dba_tables where table_name='SYSQFI';

TABLESPACE_NAME

------------------------------

IPRATBS

SQL> select EXTENT_MANAGEMENT,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces where tablespace_name='IPRATBS';

EXTENT_MANAGEMENT SEGMENT_SPACE_MANAGEMENT

----------------- ------------------------

LOCAL AUTO

SQL> select file_id,block_id from dba_extents where segment_name='SYSQFI';

FILE_ID BLOCK_ID

---------- ----------

10 157321

SQL> select header_file,header_block from dba_segments where segment_name='SYSQFI';

HEADER_FILE HEADER_BLOCK

----------- ------------

10 157324

我们分别dump 10-157321，10-157322，10-157323，10-157324这4个block：

10-157321的内容：

Start dump data blocks tsn: 8 file#: 10 minblk 157321 maxblk 157321

buffer tsn: 8 rdba: 0x02826689 (10/157321)

scn: 0x0002.e9c77e31 seq: 0x01 flg: 0x04 tail: 0x7e312001

frmt: 0x02 chkval: 0xfffb type: 0x20=FIRST LEVEL BITMAP BLOCK

Hex dump of block: st=0, typ_found=1

......中间内容省略

Dump of First Level Bitmap Block

--------------------------------

nbits : 4 nranges: 1 parent dba: 0x0282668b poffset: 0

unformatted: 12 total: 64 first useful block: 4

owning instance : 1

instance ownership changed at 05/21/2008 14:36:54

Last successful Search 05/21/2008 14:36:54

Freeness Status: nf1 0 nf2 0 nf3 0 nf4 48

Extent Map Block Offset: 4294967295

First free datablock : 4

Bitmap block lock opcode 0

Locker xid: : 0x0000.000.00000000

Inc #: 0 Objd: 66347

HWM Flag: Not Set

Highwater:: 0x028266c9 ext#: 0 blk#: 64 ext size: 128

#blocks in seg. hdr's freelists: 0

#blocks below: 60

mapblk 0x00000000 offset: 0

--------------------------------------------------------

DBA Ranges :

--------------------------------------------------------

0x02826689 Length: 64 Offset: 0

0:Metadata 1:Metadata 2:Metadata 3:Metadata

4:unformatted 5:unformatted 6:unformatted 7:unformatted

8:unformatted 9:unformatted 10:unformatted 11:unformatted

12:unformatted 13:unformatted 14:unformatted 15:unformatted

16:75-100% free 17:75-100% free 18:75-100% free 19:75-100% free

20:75-100% free 21:75-100% free 22:75-100% free 23:75-100% free

24:75-100% free 25:75-100% free 26:75-100% free 27:75-100% free

28:75-100% free 29:75-100% free 30:75-100% free 31:75-100% free

32:75-100% free 33:75-100% free 34:75-100% free 35:75-100% free

36:75-100% free 37:75-100% free 38:75-100% free 39:75-100% free

40:75-100% free 41:75-100% free 42:75-100% free 43:75-100% free

44:75-100% free 45:75-100% free 46:75-100% free 47:75-100% free

48:75-100% free 49:75-100% free 50:75-100% free 51:75-100% free

52:75-100% free 53:75-100% free 54:75-100% free 55:75-100% free

56:75-100% free 57:75-100% free 58:75-100% free 59:75-100% free

60:75-100% free 61:75-100% free 62:75-100% free 63:75-100% free

--------------------------------------------------------

End dump data blocks tsn: 8 file#: 10 minblk 157321 maxblk 157321

10-157322的内容：

Start dump data blocks tsn: 8 file#: 10 minblk 157322 maxblk 157322

buffer tsn: 8 rdba: 0x0282668a (10/157322)

scn: 0x0002.d2628ee7 seq: 0x02 flg: 0x04 tail: 0x8ee72002

frmt: 0x02 chkval: 0x8357 type: 0x20=FIRST LEVEL BITMAP BLOCK

Hex dump of block: st=0, typ_found=1

......中间内容省略

Dump of First Level Bitmap Block

--------------------------------

nbits : 4 nranges: 1 parent dba: 0x0282668b poffset: 1

unformatted: 64 total: 64 first useful block: 0

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 : 0

Bitmap block lock opcode 9

Locker xid: : 0x0005.005.0000319a

Inc #: 0 Objd: 66347

--------------------------------------------------------

DBA Ranges :

--------------------------------------------------------

0x028266c9 Length: 64 Offset: 0

0:unformatted 1:unformatted 2:unformatted 3:unformatted

4:unformatted 5:unformatted 6:unformatted 7:unformatted

8:unformatted 9:unformatted 10:unformatted 11:unformatted

12:unformatted 13:unformatted 14:unformatted 15:unformatted

16:unformatted 17:unformatted 18:unformatted 19:unformatted

20:unformatted 21:unformatted 22:unformatted 23:unformatted

24:unformatted 25:unformatted 26:unformatted 27:unformatted

28:unformatted 29:unformatted 30:unformatted 31:unformatted

32:unformatted 33:unformatted 34:unformatted 35:unformatted

36:unformatted 37:unformatted 38:unformatted 39:unformatted

40:unformatted 41:unformatted 42:unformatted 43:unformatted

44:unformatted 45:unformatted 46:unformatted 47:unformatted

48:unformatted 49:unformatted 50:unformatted 51:unformatted

52:unformatted 53:unformatted 54:unformatted 55:unformatted

56:unformatted 57:unformatted 58:unformatted 59:unformatted

60:unformatted 61:unformatted 62:unformatted 63:unformatted

--------------------------------------------------------

End dump data blocks tsn: 8 file#: 10 minblk 157322 maxblk 157322

10-157323的内容：

Start dump data blocks tsn: 8 file#: 10 minblk 157323 maxblk 157323

buffer tsn: 8 rdba: 0x0282668b (10/157323)

scn: 0x0002.d2628f06 seq: 0x01 flg: 0x04 tail: 0x8f062101

frmt: 0x02 chkval: 0xd0e8 type: 0x21=SECOND LEVEL BITMAP BLOCK

Hex dump of block: st=0, typ_found=1

......中间内容省略

Dump of Second Level Bitmap Block

number: 2 nfree: 2 ffree: 0 pdba: 0x0282668c

Inc #: 0 Objd: 66347

opcode:0

xid:

L1 Ranges :

--------------------------------------------------------

0x02826689 Free: 5 Inst: 1

0x0282668a Free: 5 Inst: 1

--------------------------------------------------------

End dump data blocks tsn: 8 file#: 10 minblk 157323 maxblk 157323

10-157324的内容：

Start dump data blocks tsn: 8 file#: 10 minblk 157324 maxblk 157324

buffer tsn: 8 rdba: 0x0282668c (10/157324)

scn: 0x0002.d2628f06 seq: 0x01 flg: 0x04 tail: 0x8f062301

frmt: 0x02 chkval: 0xfe79 type: 0x23=PAGETABLE SEGMENT HEADER

Hex dump of block: st=0, typ_found=1

......中间内容省略

Extent Control Header

-----------------------------------------------------------------

Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 128

last map 0x00000000 #maps: 0 offset: 2716

Highwater:: 0x028266c9 ext#: 0 blk#: 64 ext size: 128

#blocks in seg. hdr's freelists: 0

#blocks below: 60

mapblk 0x00000000 offset: 0

Unlocked

--------------------------------------------------------

Low HighWater Mark :

Highwater:: 0x0282668d ext#: 0 blk#: 4 ext size: 128

#blocks in seg. hdr's freelists: 0

#blocks below: 0

mapblk 0x00000000 offset: 0

Level 1 BMB for High HWM block: 0x02826689

Level 1 BMB for Low HWM block: 0x02826689

--------------------------------------------------------

Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0

L2 Array start offset: 0x00001434

First Level 3 BMB: 0x00000000

L2 Hint for inserts: 0x0282668b

Last Level 1 BMB: 0x0282668a

Last Level II BMB: 0x0282668b

Last Level III BMB: 0x00000000

Map Header:: next 0x00000000 #extents: 1 obj#: 66347 flag: 0x10000000

Inc # 0

Extent Map

-----------------------------------------------------------------

0x02826689 length: 128

Auxillary Map

--------------------------------------------------------

Extent 0 : L1 dba: 0x02826689 Data dba: 0x0282668d

--------------------------------------------------------

Second Level Bitmap block DBAs

--------------------------------------------------------

DBA 1: 0x0282668b

End dump data blocks tsn: 8 file#: 10 minblk 157324 maxblk 157324

从结果里可以看到对于表SYSQFI，它的段头是有4个block，这 4个block里有两个FIRST LEVEL BITMAP BLOCK，第二个FIRST LEVEL BITMAP BLOCK里全部是空块。这个从10-157321和10-157322的dump内容中可以很清晰的看出来，10-157321里面有如下的内容：

first useful block: 4