Basic Calculation Method for the internal offset of data blocks

Reprinted please indicate the source: http://blog.csdn.net/guoyjoe/article/details/32715157

 

The BASE calculation method is as follows:
For ASSM: 76 + (itc-1) * 24 = 52 + itc * 24
For MSSM: 68 + (itc-1) * 24 = 44 + itc * 24

 

gyj@ZMDB> select * from v$type_size where component in ('KCB','KTB');COMPONEN TYPE     DESCRIPTION                       TYPE_SIZE-------- -------- -------------------------------- ----------KCB      KCBH     BLOCK COMMON HEADER                      20KTB      KTBIT    TRANSACTION VARIABLE HEADER              24KTB      KTBBH    TRANSACTION FIXED HEADER                 48KTB      KTBBH_BS TRANSACTION BLOCK BITMAP SEGMENT          8

 

1. Test ASSM first.

 

yj@ZMDB> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Productiongyj@ZMDB> create tablespace assm datafile '/u01/app/oracle/oradata/zmdb/assm01.dbf' size 50M;Tablespace created.gyj@ZMDB> create table gyj_t5(id int,name varchar2(100))  tablespace assm;Table created.gyj@ZMDB> insert into gyj_t5 values(1,'AAAAA');1 row created.gyj@ZMDB> insert into gyj_t5 values(2,'BBBBB');1 row created.gyj@ZMDB> insert into gyj_t5 values(3,'CCCCC');1 row created.gyj@ZMDB> COMMIT;Commit complete.gyj@ZMDB> alter system flush buffer_cache;System altered.gyj@ZMDB> select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block#,id,name from gyj_t5;     FILE#     BLOCK#          ID   NAME---------- ----------   ---------- ----------        10        135          1    AAAAA        10        135          2    BBBBB        10        135          3    CCCCCBBED> set file 10 block 135        FILE#           10        BLOCK#          135BBED> p kdbr[0]sb2 kdbr[0]                                 @118      8076BBED> p *kdbr[0]rowdata[24]-----------ub1 rowdata[24]                             @8176     0x2cBBED> x /rncrowdata[24]                                 @8176    -----------flag@8176: 0x2c (KDRHFL, KDRHFF, KDRHFH)lock@8177: 0x01cols@8178:    2col    0[2] @8179: 1 col    1[5] @8182: AAAAABBED> p ktbbhictsb2 ktbbhict                                @36       28176-8076=76+（itc-1) * 24= 76+（2-1）* 24=100

 

2. We will test MSSM.

gyj@ZMDB> create tablespace mssm datafile '/u01/app/oracle/oradata/zmdb/mssm01.dbf' size 50M segment space management manual;Tablespace created.gyj@ZMDB> create table gyj_mssm(id int,name varchar2(100)) tablespace mssm;Table created.gyj@ZMDB> insert into gyj_mssm values(4,'DDDDD');1 row created.gyj@ZMDB> insert into gyj_mssm values(5,'EEEEE');1 row created.gyj@ZMDB> insert into gyj_mssm values(6,'FFFFF');1 row created.gyj@ZMDB> commit;Commit complete.gyj@ZMDB> col name for a20gyj@ZMDB> select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block#,id,name from gyj_mssm;     FILE#     BLOCK#         ID NAME---------- ---------- ---------- --------------------        11        129          4 DDDDD        11        129          5 EEEEE        11        129          6 FFFFFBBED> set file 11 block 129        FILE#           11        BLOCK#          129BBED> p kdbr[0]sb2 kdbr[0]                                 @110      8084BBED> p *kdbr[0]rowdata[24]-----------ub1 rowdata[24]                             @8176     0x2cBBED> x /rncrowdata[24]                                 @8176    -----------flag@8176: 0x2c (KDRHFL, KDRHFF, KDRHFH)lock@8177: 0x01cols@8178:    2col    0[2] @8179: 4 col    1[5] @8182: DDDDDBBED> p ktbbhictsb2 ktbbhict                                @36       28176-8084=68+（itc-1) * 24=68+（2-1）*24=92

 

 

3. Why does ASSM have eight more bytes than MSSM?

************MSSMBBED> set file 11 block 129        FILE#           11        BLOCK#          129BBED> map /v File: /u01/app/oracle/oradata/zmdb/mssm01.dbf (11) Block: 129                                   Dba:0x02c00081------------------------------------------------------------ KTB Data Block (Table/Cluster) struct kcbh, 20 bytes                      @0           ub1 type_kcbh                           @0           ub1 frmt_kcbh                           @1           ub1 spare1_kcbh                         @2           ub1 spare2_kcbh                         @3           ub4 rdba_kcbh                           @4           ub4 bas_kcbh                            @8           ub2 wrp_kcbh                            @12          ub1 seq_kcbh                            @14          ub1 flg_kcbh                            @15          ub2 chkval_kcbh                         @16          ub2 spare3_kcbh                         @18       struct ktbbh, 72 bytes                     @20          ub1 ktbbhtyp                            @20          union ktbbhsid, 4 bytes                 @24          struct ktbbhcsc, 8 bytes                @28          sb2 ktbbhict                            @36          ub1 ktbbhflg                            @38          ub1 ktbbhfsl                            @39          ub4 ktbbhfnx                            @40          struct ktbbhitl[2], 48 bytes            @44       struct kdbh, 14 bytes                      @92          ub1 kdbhflag                            @92          sb1 kdbhntab                            @93          sb2 kdbhnrow                            @94          sb2 kdbhfrre                            @96          sb2 kdbhfsbo                            @98          sb2 kdbhfseo                            @100         sb2 kdbhavsp                            @102         sb2 kdbhtosp                            @104      struct kdbt[1], 4 bytes                    @106         sb2 kdbtoffs                            @106         sb2 kdbtnrow                            @108      sb2 kdbr[3]                                @110      ub1 freespace[8036]                        @116      ub1 rowdata[36]                            @8152     ub4 tailchk                                @8188*****************ASSMBBED> set file 10 block 135        FILE#           10        BLOCK#          135File: /u01/app/oracle/oradata/zmdb/assm01.dbf (10) Block: 141                                   Dba:0x0280008d------------------------------------------------------------ KTB Data Block (Table/Cluster) struct kcbh, 20 bytes                      @0           ub1 type_kcbh                           @0           ub1 frmt_kcbh                           @1           ub1 spare1_kcbh                         @2           ub1 spare2_kcbh                         @3           ub4 rdba_kcbh                           @4           ub4 bas_kcbh                            @8           ub2 wrp_kcbh                            @12          ub1 seq_kcbh                            @14          ub1 flg_kcbh                            @15          ub2 chkval_kcbh                         @16          ub2 spare3_kcbh                         @18       struct ktbbh, 72 bytes                     @20          ub1 ktbbhtyp                            @20          union ktbbhsid, 4 bytes                 @24          struct ktbbhcsc, 8 bytes                @28          sb2 ktbbhict                            @36          ub1 ktbbhflg                            @38          ub1 ktbbhfsl                            @39          ub4 ktbbhfnx                            @40          struct ktbbhitl[2], 48 bytes            @44       struct kdbh, 14 bytes                      @100         ub1 kdbhflag                            @100         sb1 kdbhntab                            @101         sb2 kdbhnrow                            @102         sb2 kdbhfrre                            @104         sb2 kdbhfsbo                            @106         sb2 kdbhfseo                            @108         sb2 kdbhavsp                            @110         sb2 kdbhtosp                            @112      struct kdbt[1], 4 bytes                    @114         sb2 kdbtoffs                            @114         sb2 kdbtnrow                            @116      sb2 kdbr[3]                                @118      ub1 freespace[8028]                        @124      ub1 rowdata[36]                            @8152     ub4 tailchk                                @8188  

 

Comparison
----- MSSM
Struct ktbbhitl [2], 48 bytes @ 44

Struct kdbh, 14 bytes @ 92

--- ASSM
Struct ktbbhitl [2], 48 bytes @ 44

Struct kdbh, 14 bytes @ 100
 
@ 92 ----> @ 100 the offset of kdbh has changed and 8 bytes are added.