转载请注明出处：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 20ktb ktbit transaction variable header 24ktb ktbbh transaction fixed header 48ktb 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 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、我们对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、为什么assm要比mssm多了8个字节
************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
对比
struct kdbh, 14 bytes @92
---assm
struct kdbh, 14 bytes @100