数据块内部偏移量的基本计算方法

转载请注明出处：http://blog.csdn.net/guoyjoe/article/details/32715157 BASE的计算方法为： gyj@ZMDB select * from v$type_size where component in (KCB,KTB);COMPONEN TYPE DESCRIPTION TYPE_SIZE-------- -------- --------------------------------

转载请注明出处：http://blog.csdn.net/guoyjoe/article/details/32715157

BASE的计算方法为：

gyj@ZMDB> select * from v$type_size where component in ('KCB','KTB');

COMPONEN TYPE DESCRIPTION TYPE_SIZE
-------- -------- -------------------------------- ----------
KCB KCBH BLOCK COMMON HEADER 20
KTB KTBIT TRANSACTION VARIABLE HEADER 24
KTB KTBBH TRANSACTION FIXED HEADER 48
KTB KTBBH_BS TRANSACTION BLOCK BITMAP SEGMENT 8

1、我们先对ASSM做测试

yj@ZMDB> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

gyj@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 CCCCC


BBED> set file 10 block 135
 FILE# 10
 BLOCK# 135

BBED> p kdbr[0]
sb2 kdbr[0] @118 8076

BBED> p *kdbr[0]
rowdata[24]
-----------
ub1 rowdata[24] @8176 0x2c

BBED> x /rnc
rowdata[24] @8176 
-----------
flag@8176: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8177: 0x01
cols@8178: 2

col 0[2] @8179: 1 
col 1[5] @8182: AAAAA

BBED> p ktbbhict
sb2 ktbbhict @36 2

8176-8076=76+（itc-1) * 24= 76+（2-1）* 24=100

2、我们对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 a20
gyj@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 FFFFF

BBED> set file 11 block 129
 FILE# 11
 BLOCK# 129

BBED> p kdbr[0]
sb2 kdbr[0] @110 8084

BBED> p *kdbr[0]
rowdata[24]
-----------
ub1 rowdata[24] @8176 0x2c

BBED> x /rnc
rowdata[24] @8176 
-----------
flag@8176: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8177: 0x01
cols@8178: 2

col 0[2] @8179: 4 
col 1[5] @8182: DDDDD

BBED> p ktbbhict
sb2 ktbbhict @36 2

8176-8084=68+（itc-1) * 24=68+（2-1）*24=92

3、为什么ASSM要比MSSM多了8个字节

************MSSM
BBED> set file 11 block 129
 FILE# 11
 BLOCK# 129

BBED> 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

*****************ASSM
BBED> set file 10 block 135
 FILE# 10
 BLOCK# 135

File: /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 

对比

struct kdbh, 14 bytes @92

---ASSM

struct kdbh, 14 bytes @100