ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Data Blocks, Extents, and Segment

Data Blocks, Extents, and Segment

原创 Linux操作系统 作者:lixunix 时间:2012-04-05 15:49:01 0 删除 编辑

如果要分析块结构以及区段的内部结构,那么BBED将会是很好的工具,下面介绍如何安装BBED

9i/10g中连接生成BBED:

           cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk $ORACLE_HOME/rdbms/lib/bbed
如果先直接安装到$ORACLE_HOME/bin目录,则:
  make -f ins_rdbms.mk BBED=$ORACLE_HOME/bin/bbed 
  然后可以直接输入命令BBED,模式的密码是:blockedit
  先创建一张表TEST,并且插入一些数据,查看此表目前有几个区:
 SQL>  select SEGMENT_NAME,extent_id,block_id from dba_extents where wner='TEST'
2    and segment_name='T2';
   SEGMENT_NAME  EXTENT_ID   BLOCK_ID
       T2           0         57
T2           1         65
T2           2         73
T2           3         81
T2           4         89
T2           5         97
数据块(DBA
0
57
58
59
60
61
62
63
64
 
OX10000039
0x3a     
0x3b
0x3c
0x3d
0x3e
0x3f 
0x40
1
65
66
67
68
69
70
71
72
 
0X10000041
0x42
0x43
0x44
0x45
0x46
0x47
0x48
2
73
74
75
76
77
78
79
80
 
0x10000049
0x4a
0x4b
0x4c
0x4d
0x4e
0x4f
0x50
3
81
82
83
84
85
86
87
88
 
0x10000051
0x52
0x53
0x54
0x55
0x56
0x57
0x58
4
89
90
91
92
93
94
95
96
 
0x10000059
0x5a
0x5b
0x5c
0x5d
0x5e
0x5f
0x60
5
97
98
99
100
101
102
103
104
 
0x10000061
0x62
0x63
0x64
0x65
0x66
0x67
0x68
 首先DUMP0,块57,也就是第一级位图块
Dump of First Level Bitmap Block
 --------------------------------
   nbits : 4 nranges: 2         parent dba:  0x0100003a   poffset: 0
   unformatted: 0       total: 16        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 : 16
   Bitmap block lock opcode 0
   Locker xid:     :  0x0000.000.00000000
   Inc #: 0 Objd: 47820
  --------------------------------------------------------
  DBA Ranges :
  --------------------------------------------------------
   0x01000039  Length: 8      Offset: 0
   0x01000041  Length: 8      Offset: 8 
----管理两个区
但是随着段的增大,一个BMB管理的区也会不同:

                     Segment Size     Blocks Mapped

<= 1MB        16 Data Blocks

<= 32MB       64 Data Blocks

<= 1GB       256 Data Blocks

> 1GB      1024 Data Blocks
 
 
   0:Metadata   1:Metadata   2:Metadata   3:FULL
   4:FULL   5:FULL   6:FULL   7:FULL
   8:FULL   9:FULL   10:FULL   11:FULL
   12:FULL   13:FULL   14:FULL   15:FULL
                                            --------管理的这两个区都满了 
  --------------------------------------------------------
End dump data blocks tsn: 4 file#: 4 minblk 57 maxblk 57
然后DUMP58,是二级位图块
Start dump data blocks tsn: 4 file#: 4 minblk 58 maxblk 58
buffer tsn: 4 rdba: 0x0100003a (4/58)
scn: 0x0000.0018658c seq: 0x02 flg: 0x04 tail: 0x658c2102
frmt: 0x02 chkval: 0x3cd8 type: 0x21=SECOND LEVEL BITMAP BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0DF58400 to 0x0DF5A400
DF58400 0000A221 0100003A 0018658C 04020000  [!...:....e......]
DF58410 00003CD8 00000000 00000000 00000000  [.<..............]
DF58420 00000000 00000000 00000000 00000000  [................]
        Repeat 1 times
DF58440 00000000 00000000 00000000 0100003B  [............;...]
DF58450 00000003 00000001 00000002 00000000  [................]
DF58460 00000000 00000000 0000BACC 00000001  [................]
DF58470 00000000 01000039 00010001 01000049  [....9.......I...]
DF58480 00010001 01000059 00010005 00000000  [....Y...........]
DF58490 00000000 00000000 00000000 00000000  [................]
        Repeat 501 times
DF5A3F0 00000000 00000000 00000000 658C2102  [.............!.e]
Dump of Second Level Bitmap Block
   number: 3       nfree: 1       ffree: 2      pdba:     0x0100003b
   Inc #: 0 Objd: 47820
  opcode:0
 xid:
  L1 Ranges :
  --------------------------------------------------------
   0x01000039  Free: 1 Inst: 1    ----------inst1指单实例数据库
   0x01000049  Free: 1 Inst: 1    ----------第二个位图块的地址
   0x01000059  Free: 5 Inst: 1    ----------第三个位图块的地址
                   其中5代表最大的FS75%-100%之间
  --------------------------------------------------------
End dump data blocks tsn: 4 file#: 4 minblk 58 maxblk 58

DUMP59,也就是SEGMENT HEADER

Extent Control Header

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

  Extent Header:: spare1: 0      spare2: 0      #extents: 6      #blocks: 48

                  last map  0x00000000  #maps: 0      offset: 2716

      Highwater::  0x01000066  ext#: 5      blk#: 5      ext size: 8

  #blocks in seg. hdr's freelists: 0

  #blocks below: 45

  mapblk  0x00000000  offset: 5

                   Unlocked

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

  Low HighWater Mark :

      Highwater::  0x01000066  ext#: 5      blk#: 5      ext size: 8

                          ------------高水位标记线也就是块102.此块之间的所有块都已经被格式化了

  #blocks in seg. hdr's freelists: 0

  #blocks below: 45

  mapblk  0x00000000  offset: 5

  Level 1 BMB for High HWM block: 0x01000059

  Level 1 BMB for Low HWM block: 0x01000059

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

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

  Last Level II BMB:  0x0100003a

  Last Level III BMB:  0x00000000

     Map Header:: next  0x00000000  #extents: 6    obj#: 47820  flag: 0x10000000

  Inc # 0

  Extent Map

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

   0x01000039  length: 8

   0x01000041  length: 8

   0x01000049  length: 8

   0x01000051  length: 8

   0x01000059  length: 8

   0x01000061  length: 8

 

  Auxillary Map

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

   Extent 0     :  L1 dba:  0x01000039 Data dba:  0x0100003c

   Extent 1     :  L1 dba:  0x01000039 Data dba:  0x01000041

   Extent 2     :  L1 dba:  0x01000049 Data dba:  0x0100004a

   Extent 3     :  L1 dba:  0x01000049 Data dba:  0x01000051

   Extent 4     :  L1 dba:  0x01000059 Data dba:  0x0100005a

   Extent 5     :  L1 dba:  0x01000059 Data dba:  0x01000061

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

 

   Second Level Bitmap block DBAs

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

   DBA 1:   0x0100003a

 

End dump data blocks tsn: 4 file#: 4 minblk 59 maxblk 59

从上面可以看出DBA0X1000049,也就是块73,是一级BMB块,下面分别DUMP这两个块。

首先DUMP DBA0X1000049的块

Start dump data blocks tsn: 4 file#: 4 minblk 73 maxblk 73

buffer tsn: 4 rdba: 0x01000049 (4/73)

scn: 0x0000.0018658c seq: 0x01 flg: 0x04 tail: 0x658c2001

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

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

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

Dump of First Level Bitmap Block

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

   nbits : 4 nranges: 2         parent dba:  0x0100003a   poffset: 1

   unformatted: 0       total: 16        first useful block: 1

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

   Bitmap block lock opcode 0

   Locker xid:     :  0x0000.000.00000000

   Inc #: 0 Objd: 47820

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

  DBA Ranges :

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

   0x01000049  Length: 8      Offset: 0

   0x01000051  Length: 8      Offset: 8

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

   4:FULL     5:FULL     6:FULL   7:FULL

   8:FULL     9:FULL    10:FULL   11:FULL

   12:FULL   13:FULL    14:FULL   15:FULL

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

End dump data blocks tsn: 4 file#: 4 minblk 73 maxblk 73

接下来看DBA0x01000059的块,也是一级块。

Start dump data blocks tsn: 4 file#: 4 minblk 89 maxblk 89

buffer tsn: 4 rdba: 0x01000059 (4/89)

scn: 0x0000.0018658c seq: 0x03 flg: 0x04 tail: 0x658c2003

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

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

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

Dump of First Level Bitmap Block

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

   nbits : 4 nranges: 2         parent dba:  0x0100003a   poffset: 2

   unformatted: 3       total: 16        first useful block: 1

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

   Bitmap block lock opcode 0

   Locker xid:     :  0x0000.000.00000000

   Inc #: 0 Objd: 47820

  HWM Flag: HWM Set

      Highwater::  0x01000066  ext#: 5      blk#: 5      ext size: 8

  #blocks in seg. hdr's freelists: 0

  #blocks below: 45

  mapblk  0x00000000  offset: 5

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

  DBA Ranges :

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

   0x01000059  Length: 8      Offset: 0

   0x01000061  Length: 8      Offset: 8

 

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

   4:FULL        5:FULL           6:FULL   7:FULL

   8:FULL        9:FULL          10:FULL   11:FULL

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

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

End dump data blocks tsn: 4 file#: 4 minblk 89 maxblk 89

可以看出有第三个一级位图块管理的块中,块102,103,104块是没有被各式化的,可以DUMP出来看看。

下面DUMP102

Start dump data blocks tsn: 4 file#: 4 minblk 102 maxblk 102

buffer tsn: 4 rdba: 0x00000066 (0/102)

scn: 0x0000.00000000 seq: 0x01 flg: 0x05 tail: 0x00000001

frmt: 0x02 chkval: 0xa766 type: 0x00=unknown

Hex dump of corrupt header 4 = CORRUPT

Dump of memory from 0x0CECD400 to 0x0CECD414

CECD400 0000A200 00000066 00000000 05010000  [....f...........]

CECD410 0000A766                             [f...]

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

Dump of memory from 0x0CECD400 to 0x0CECF400

CECD400 0000A200 00000066 00000000 05010000  [....f...........]

CECD410 0000A766 00000000 00000000 00000000  [f...............]

CECD420 00000000 00000000 00000000 00000000  [................]

        Repeat 508 times

CECF3F0 00000000 00000000 00000000 00000001  [................]

End dump data blocks tsn: 4 file#: 4 minblk 102 maxblk 102

 

 

 

 

 

数据块析

下面再分析数据块,从上面可以看出数据库块开始的位置应该是DBAOX1000003C,也就是块60.DUMP的结果如下:

Start dump data blocks tsn: 4 file#: 4 minblk 60 maxblk 60

buffer tsn: 4 rdba: 0x0100003c (4/60)

scn: 0x0000.0018657d seq: 0x02 flg: 0x04 tail: 0x657d0602

frmt: 0x02 chkval: 0x35d6 type: 0x06=trans data

-----------------------省略------------------------

Block header dump:  0x0100003c

 Object id on Block? Y

 seg/obj: 0xbacc  csc: 0x00.18657c  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   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.0018657c

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

===============

tsiz: 0x1f80

hsiz: 0x54

pbl: 0x0d06647c

bdba: 0x0100003c

     76543210

flag=--------

ntab=1

nrow=33

frre=-1

fsbo=0x54

fseo=0x42a

avsp=0x3d6

tosp=0x3d6

0xe:pti[0]      nrow=33 ffs=0

0x12:pri[0]     ffs=0x1eaa

0x14:pri[1]     ffs=0x1dda

0x16:pri[2]     ffs=0x1d0b

0x18:pri[3]     offs=0x1c35

0x1a:pri[4]     ffs=0x1b66

0x1c:pri[5]     ffs=0x1a8f

0x1e:pri[6]     ffs=0x19ba

0x20:pri[7]     ffs=0x18dd

0x22:pri[8]     ffs=0x1806

0x24:pri[9]     ffs=0x1733

0x26:pri[10]    ffs=0x1662

0x28:pri[11]    ffs=0x1592

0x2a:pri[12]    ffs=0x14be

0x2c:pri[13]    ffs=0x13f0

0x2e:pri[14]    ffs=0x131a

0x30:pri[15]    ffs=0x1246

0x32:pri[16]    ffs=0x1173

0x34:pri[17]    ffs=0x109d

0x36:pri[18]    ffs=0xfc7

0x38:pri[19]    ffs=0xef1

0x3a:pri[20]    ffs=0xe24

0x3c:pri[21]    ffs=0xd51

0x3e:pri[22]    ffs=0xc7e

0x40:pri[23]    ffs=0xbac

0x42:pri[24]    ffs=0xad5

0x44:pri[25]    ffs=0xa06

0x46:pri[26]    ffs=0x936

0x48:pri[27]    ffs=0x860

0x4a:pri[28]    ffs=0x784

0x4c:pri[29]    ffs=0x6ae

0x4e:pri[30]    ffs=0x5d8

0x50:pri[31]    ffs=0x500

0x52:pri[32]    ffs=0x42a

block_row_dump:

tab 0, row 0, @0x1eaa

tl: 214 fb: --H-FL-- lb: 0x0  cc: 49

col  0: [ 3]  53 59 53

col  1: [ 5]  49 43 4f 4c 24

col  2: [ 6]  53 59 53 54 45 4d

----------------------省略-----------------------

end_of_block_dump

End dump data blocks tsn: 4 file#: 4 minblk 60 maxblk 60

下面用BBED来分析此数据块

EBED>set dba 4,60

BBED> map          ----------DATAFILE 4 BLOCK 60

 File: /opt/ora10g/oradata/RACDB2/datafile/o1_mf_users_632vpvcc_.dbf (4)

 Block: 60                                    Dba:0x0100003c

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

 KTB Data Block (Table/Cluster)

 

 struct kcbh, 20 bytes                         @0      

 

 struct ktbbh, 96 bytes                        @20     

 

 struct kdbh, 14 bytes                         @124    

 

 struct kdbt[1], 4 bytes                        @138    

 

 sb2 kdbr[33]                               @142    

 

 ub1 freespace[982]                          @208    

 

 ub1 rowdata[6998]                          @1190   

 

 ub4 tailchk                                @8188   

 

下面逐个分析上面的几个部分:

一、CACHE LAYER

    BBED> p kcbh

struct kcbh, 20 bytes                          @0      

   ub1 type_kcbh                            @0        0x06

   ub1 frmt_kcbh                            @1        0xa2

   ub1 spare1_kcbh                          @2        0x00

   ub1 spare2_kcbh                          @3        0x00

   ub4 rdba_kcbh                            @4        0x0100003c

   ub4 bas_kcbh                             @8        0x0018657d

   ub2 wrp_kcbh                             @12       0x0000

   ub1 seq_kcbh                             @14       0x02

   ub1 flg_kcbh                             @15       0x04 (KCBHFCKV)

   ub2 chkval_kcbh                          @16       0x35d6

   ub2 spare3_kcbh                          @18       0x0000

1TYPE占用一个字节

TYPE是指此数据块的类型,其中:

01 --  UNDO SEGMENT HEADER(UNDO段头)

02 --  UNDO DATA BLOCK(UNDO 数据块)

05 --  DATA SEGMENT HAEDER(TEMP,INDEX,DATA )

06 --  KTB MANAGED DATA BLOCK(WITH ITL)我们一般的块属于这种格式

07 --  TEMP TABLE DATA BLOCK 临时表块

10 --  SEGMENT FREE LIST BLOCLK

11 --  DATA FILE HEADER(数据文件头) 比如说新建一个表空间,那么第二个块开始的那些块就属于此种格式

 

BBED> set offset 0

        OFFSET          0

 

BBED> dump

 File: /opt/ora10g/oradata/RACDB2/datafile/o1_mf_users_632vpvcc_.dbf (4)

 Block: 60               Offsets:    0 to  511           Dba:0x0100003c

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

 06a20000 3c000001 7d651800 00000204 d6350000 01000000 ccba0000 7c651800

可以看出TYPE06,也就是KTB MANAGED DATA BLOCK

1、             FORMAT格式也占用一个字节

FORMAT的值为A29I/10G以后都是此值

2、             ub1 spare1_kcbh ub1 spare2_kcbh还未被使用

3、             rdba_kcbh0x0100003c,占用四个字节。

化为二进制位:

   0000 0001 0000 0000 0000 0000 0011 1100

其中前10bit代表数据文件也就是0000 0001 00,10 10进制为4

22位代表数据块号,也就是00 0000 0000 0000 0011 1100,10机制为60

也就是此块属于4号文件第60个块

SELECT TO_NUMBER('0000003C','XXXXXXXX') FROM DUAL

60

大概重要的就是这些.

二、  TRACSACTION LAYER,包括两部分,固定长度24个字节,还有就是可变部分,长度根据itl条目的个数变化,每个itl长度为24字节,内部结构名ktbbhitl

BBED> p  ktbbh

struct ktbbh, 96 bytes                  @20     

   ub1 ktbbhtyp                      @20       0x01 (KDDBTDATA)

   union ktbbhsid, 4 bytes              @24     

      ub4 ktbbhsg1                   @24       0x0000bacc

      ub4 ktbbhod1                   @24       0x0000bacc

   struct ktbbhcsc, 8 bytes              @28     

      ub4 kscnbas                    @28       0x0018657c

      ub2 kscnwrp                    @32       0x0000

   b2 ktbbhict                        @36       3

   ub1 ktbbhflg                       @38       0x32 (NONE)

   ub1 ktbbhfsl                       @39       0x00

   ub4 ktbbhfnx                      @40       0x01000039

   struct ktbbhitl[0], 24 bytes             @44     

      struct ktbitxid, 8 bytes             @44       

         ub2 kxidusn                  @44       0xffff

         ub2 kxidslt                   @46       0x0000

         ub4 kxidsqn                  @48       0x00000000

      struct ktbituba, 8 bytes           @52     

         ub4 kubadba                @52       0x00000000

         ub2 kubaseq                @56       0x0000

         ub1 kubarec                @58       0x00

      ub2 ktbitflg                    @60       0x8000 (KTBFCOM)

      union _ktbitun, 2 bytes           @62     

         b2 _ktbitfsc                 @62       0

         ub2 _ktbitwrp               @62       0x0000

      ub4 ktbitbas                   @64       0x0018657c

   struct ktbbhitl[1], 24 bytes           @68     

      struct ktbitxid, 8 bytes            @68     

         ub2 kxidusn                 @68       0x0000

         ub2 kxidslt                  @70       0x0000

         ub4 kxidsqn                 @72       0x00000000

      struct ktbituba, 8 bytes            @76     

         ub4 kubadba                 @76       0x00000000

         ub2 kubaseq                 @80       0x0000

         ub1 kubarec                  @82       0x00

      ub2 ktbitflg                     @84       0x0000 (NONE)

      union _ktbitun, 2 bytes            @86     

         b2 _ktbitfsc                  @86       0

         ub2 _ktbitwrp                @86       0x0000

      ub4 ktbitbas                     @88       0x00000000

   struct ktbbhitl[2], 24 bytes            @92     

      struct ktbitxid, 8 bytes             @92     

         ub2 kxidusn                  @92       0x0000

         ub2 kxidslt                   @94       0x0000

         ub4 kxidsqn                  @96       0x00000000

      struct ktbituba, 8 bytes            @100    

         ub4 kubadba                 @100      0x00000000

         ub2 kubaseq                 @104      0x0000

         ub1 kubarec                  @106      0x00

      ub2 ktbitflg                     @108      0x0000 (NONE)

      union _ktbitun, 2 bytes            @110    

         b2 _ktbitfsc                  @110      0

         ub2 _ktbitwrp                @110      0x0000

      ub4 ktbitbas                     @112      0x00000000

可以看出一共有三个ITL事物槽,每个占用24个字节,一共用了72个字节,还有24个固定部分,所以TRACSACTION LAYER总共占用96个字节。分析一下其中的一个。struct ktbbhitl[0], 24 bytes

BBED> p ktbbhitl[0]

struct ktbbhitl[0], 24 bytes         @44     

   struct ktbitxid, 8 bytes           @44     

      ub2 kxidusn                  @44       0xffff

      ub2 kxidslt                    @46       0x0000

      ub4 kxidsqn                    @48       0x00000000

   struct ktbituba, 8 bytes                @52     

      ub4 kubadba                            @52       0x00000000

      ub2 kubaseq                          @56       0x0000

      ub1 kubarec                        @58       0x00

   ub2 ktbitflg                          @60       0x8000 (KTBFCOM)

   union _ktbitun, 2 bytes                  @62     

      b2 _ktbitfsc                          @62       0

      ub2 _ktbitwrp                          @62       0x0000

   ub4 ktbitbas                                @64       0x0018657c         

可以对照刚才DUMP的文件来分析,下面是DUMP出的此数据库块。

Itl           Xid              Uba          Flag  Lck        Scn/Fsc

0x01   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.0018657c

其中LOCK表示为0,SCN/FSC0X.0000.0018657C

XID -------------偏移量为@44,总共占用8个字节

UBA ------------偏移量为@52,总共占用8个字节

FLAG-------------偏移量为@61,占用一个字节,值为0X8000.0X20表示未提交,0X80表示已经提交,可以用BBEDOFFSET61然后修改一个为提交的事物为已提交)

BBED> set offset 60

        OFFSET          60

 

BBED> dump

 File: /opt/ora10g/oradata/RACDB2/datafile/o1_mf_users_632vpvcc_.dbf (4)

 Block: 60               Offsets:   60 to  571           Dba:0x0100003c

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

 00800000 7c651800 00000000 00000000 00000000 00000000 00000000 00000000

OFFSET62开始的后6个字节为SCN,也就是 00 00 7C 65 18 00

按照高字节在前,低字节在后的原理,所以SCN为:00 00 00 18 65 7C正好跟DUMP文件出来的结果吻合。

三、DATA LAYER,包括TABLE DIRECTORY ,ROW DIRECTORY,FREE SPACE

ROW DATA

BBED> p  kdbh

struct kdbh, 14 bytes                       @124    

   ub1 kdbhflag                          @124      0x00 (NONE)

   b1 kdbhntab                           @125      1

   b2 kdbhnrow                          @126      33

   sb2 kdbhfrre                          @128     -1

   sb2 kdbhfsbo                          @130      84

   sb2 kdbhfseo                          @132      1066

   b2 kdbhavsp                           @134      982

   b2 kdbhtosp                           @136      982

1、NTAB这里为1表示此表不是CLUSTERCLUSTER,此值大于1

2、NROW  NUMBER OF ROWS

3、FSBO表示FREE SPACE BEGIN OFFSET

4、FSEO 表示FREE SPACE END OFFSET

5、AVSP AVALIABLE SPACE IN THE BLOCK

6、TOSP TOTAL AVALIABLE SPACE WHEN ALL TRANSACTION COMMIT

7、TABLE DIRECTORY 用去4个字节

   BBED> p  kdbt

struct kdbt[0], 4 bytes                     @138    

   b2 kdbtoffs                              @138      0

   b2 kdbtnrow                              @140      33

8、ROW DIRECTORY 一共用去33*2=66个字节

       BBED> p kdbr

sb2 kdbr[0]                                 @142      7850

sb2 kdbr[1]                                 @144      7642

sb2 kdbr[2]                                 @146      7435

sb2 kdbr[3]                                 @148      7221

sb2 kdbr[4]                                 @150      7014

sb2 kdbr[5]                                 @152      6799

sb2 kdbr[6]                                 @154      6586

sb2 kdbr[7]                                 @156      6365

sb2 kdbr[8]                                 @158      6150

sb2 kdbr[9]                                 @160      5939

sb2 kdbr[10]                                @162      5730

sb2 kdbr[11]                                @164      5522

sb2 kdbr[12]                                @166      5310

sb2 kdbr[13]                                @168      5104

sb2 kdbr[14]                                @170      4890

sb2 kdbr[15]                                @172      4678

sb2 kdbr[16]                                @174      4467

sb2 kdbr[17]                                @176      4253

sb2 kdbr[18]                                @178      4039

sb2 kdbr[19]                                @180      3825

sb2 kdbr[20]                                @182      3620

sb2 kdbr[21]                                @184      3409

sb2 kdbr[22]                                @186      3198

sb2 kdbr[23]                                @188      2988

sb2 kdbr[24]                                @190      2773

sb2 kdbr[25]                                @192      2566

sb2 kdbr[26]                                @194      2358

sb2 kdbr[27]                                @196      2144

sb2 kdbr[28]                                @198      1924

sb2 kdbr[29]                                @200      1710

sb2 kdbr[30]                                @202      1496

sb2 kdbr[31]                                @204      1280

sb2 kdbr[32]                                @206      1066

9BBED> p tailchk    -------正好占用四字节8188-8191

ub4 tailchk                               @8188     0x657d0602

 

行迁移和行链接

首先创建表T

SQL> desc t;

 Name                        Null?    Type

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

 ID                                   NUMBER

 A                                    VARCHAR2(4000)

 B                                    VARCHAR2(4000)

 C                                    VARCHAR2(4000)

SQL> insert into t values(1,dbms_random.string('P',4000),'b','c');

(表T的一级位图在DATAFILE 4 BLOCK 113

SQL>  alter system dump datafile 4 block 113;

DBA Ranges :

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

   0x01000071  Length: 8      Offset: 0

 

   0:Metadata   1:Metadata   2:Metadata   3:75-100% free

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

        可以看出块119现在的剩余的空间为25%-50%,用BBED察看一下

     BBED> set dba 4,119  

        DBA             0x01000077 (16777335 4,119)

 

BBED> map

 File: /opt/ora10g/oradata/RACDB1/users01.dbf (4)

 Block: 119                                   Dba:0x01000077

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

 KTB Data Block (Table/Cluster)

 

 struct kcbh, 20 bytes                      @0      

 

 struct ktbbh, 72 bytes                     @20     

 

 struct kdbh, 14 bytes                      @100    

 

 struct kdbt[1], 4 bytes                    @114    

 

 sb2 kdbr[1]                                @118    

 

 ub1 freespace[4055]                        @120    

 

 ub1 rowdata[4013]                          @4175   

 

 ub4 tailchk                                @8188   

数据区从OFFSET 4175开始,DUMP出来看看

BBED> set offset 4175

        OFFSET          4175

 

BBED> dump

 File: /opt/ora10g/oradata/RACDB1/users01.dbf (4)

 Block: 119              Offsets: 4175 to 4686           Dba:0x01000077

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

 2c020402 c306fea0 0f5a4f24 6f692a5f 7b69677a 674a7a3a 2d47752f 364d5430

 51465432 775e7d46 2f2d5b53 61517c72 6652644e 2d33515b 49483268 2f3c262b

 下面DUMP4,119看看

block_row_dump:

tab 0, row 0, @0xfeb

tl: 4013 fb: --H-FL-- lb: 0x2  cc: 4

col  0: [ 2]  c3 06                  ----------------注下面

col  1: [4000]

 5a 4f 24 6f 69 2a 5f 7b 69 67 7a 67 4a 7a 3a 2d 47 75 2f 36 4d 54 30 51 46

其中lb:0x2表示事物槽0X2上面的锁标识为1

cc:0x4 表示TABLE T有几个字段,这里有四个字段,最大值为255

接下来的02表示字段ID长度为2

然后看蓝色部分,即c306表示的是字段ID的值,这里为50000

OFFSET 4184开始的是第二个字段的值。

下面将模拟行迁移,由于更新行的时候,在一个BLOCK中已经不能存放所有的字段将会产生行签约

首先要运行$ORACLE_HOME/rdbms/admin/utlchain.sql,TEST用户下将会出现表CHAINED_ROWS

SQL> select * from CHAINED_ROWS;

 

no rows selected

下面更新表T

UPDATE T SET B=DBMS_RANDOM.STRING('P',1000)

然后再次查看块113

   0:Metadata   1:Metadata   2:Metadata   3:75-100% free

   4:75-100% free   5:75-100% free   6:75-100% free   7:75-100% free

竟然变成了有75%-100%的空闲空间

BBED查看

BBED> MAP       

 File: /opt/ora10g/oradata/RACDB1/users01.dbf (4)

 Block: 119                                   Dba:0x01000077

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

 KTB Data Block (Table/Cluster)

 

 struct kcbh, 20 bytes                      @0       

 

 struct ktbbh, 72 bytes                     @20     

 

 struct kdbh, 14 bytes                      @100    

 

 struct kdbt[1], 4 bytes                    @114    

 

 sb2 kdbr[1]                                @118    

 

 ub1 freespace[3054]                        @120    

 

 ub1 rowdata[5014]                          @3174   

 

 ub4 tailchk                                @8188

数据确实减少了,那么是否真的产生了块迁移呢,现在可以分析表了

SQL> TRUNCATE TABLE CHAINED_ROWS;

 

Table truncated.

 

SQL> ANALYZE TABLE T LIST CHAINED ROWS;

 

Table analyzed.

 

SQL> SELECT * FROM CHAINED_ROWS;

 

OWNER_NAME                     TABLE_NAME

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

CLUSTER_NAME                   PARTITION_NAME

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

SUBPARTITION_NAME              HEAD_ROWID         ANALYZE_T

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

TEST                           T

 

N/A                            AAAMr7AAEAAAAB3AAA 18-AUG-10   

下面看看此ROWID指向的块是否为119

 

SQL> SELECT DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) FROM T;

 

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)

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

                                 119

确实如此。

行链接产生是由于在往一个块中插入一条数据的时候由于,所有的字段不能在存放在一个块中于是产生了行链接。

那么如何解决行迁移的问题呢,除了增大PCTFREE或者增加块的大小,还可以用MOVE命令来清除,ALTER TABLE TABLE_NAME MOVE TABLESPACE TABLESPACE_NAME,或者IMP/EXP,但是如果出现上面的列子中的情况是没法清除的,表T中插入的时候有4000个字节,块119中剩余的空间为0%-25%,接着更新表T,使得字段b,C都为4000个字节,那么除了增大块的大小,否则所会导致新的行链接出现。因为一个块中放不下16000个字节。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22150599/viewspace-720378/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2011-08-22

  • 博文量
    1
  • 访问量
    2636