create table INITRANS参数分析

1. 内容介绍

Oracle数据库create table时使用INITRANS参数设置数据块ITL事务槽的数量,确保该数据块上 并发事务数量。参数内容总结如下, 1. Oracle 8K blocksize 数据块初始 2个itl,8K blocksize 数据块最多169个itl,16K blocksize 数据块最多256个itl。 2. type_kcbh(offset 0): 0x06 表示为数据块,ktbbhtyp(offset 20): Typ: 1=DATA; 2=INDEX 3. offset 36: itl数量,40(before offset of ktbbhitl) + offset[offset36 * 24] + 8(ktbbh和kdbh存在8offset): kdbh结构的offset, 4. offsetOfKdbh+1: tab的数量,cluster表可能大于1,堆表等于1. 5. offsetOfKdbh+2: row的数量.

2.环境准备

create user hsql identified by hsql; grant connect,resource,dba to hsql; drop tablespace hsql including contents and datafiles; create tablespace hsql datafile '/data2/enmo/hsql01.dbf' size 10M autoextend on; create tablespace hsql2 datafile '/data2/enmo/hsql02.dbf' size 10M BLOCKSIZE 16K; drop table hsql.drop_1 purge; create table hsql.drop_1(c_char1 char(10),c_char2 char(10)) tablespace hsql INITRANS 1; create table hsql.drop_2(c_char1 char(10),c_char2 char(10)) tablespace hsql INITRANS 2; create table hsql.drop_3(c_char1 char(10),c_char2 char(10)) tablespace hsql INITRANS 3; create table hsql.drop_4(c_char1 char(10),c_char2 char(10)) tablespace hsql INITRANS 4; create table hsql.drop_5(c_char1 char(10),c_char2 char(10)) tablespace hsql INITRANS 5; create table hsql.drop_254(c_char1 char(10),c_char2 char(10)) tablespace hsql INITRANS 254; create table hsql.drop_255(c_char1 char(10),c_char2 char(10)) tablespace hsql INITRANS 255; begin for i in 1 .. 1000 loop insert into hsql.drop_1 values(i,'orastar'); insert into hsql.drop_2 values(i,'orastar'); insert into hsql.drop_3 values(i,'orastar'); insert into hsql.drop_4 values(i,'orastar'); insert into hsql.drop_5 values(i,'orastar'); insert into hsql.drop_254 values(i,'orastar'); insert into hsql.drop_255 values(i,'orastar'); end loop; commit; end; / alter system flush buffer_cache; alter system flush buffer_cache; select count(1) from hsql.drop_1;

3. 检查数据字典信息

set linesize 200 pagesize 200 col owner for a10 col segment_name for a10 select owner,segment_name,header_file,header_block,SEGMENT_TYPE from dba_segments where segment_name like 'DROP_%'; OWNER SEGMENT_NA HEADER_FILE HEADER_BLOCK SEGMENT_TYPE ---------- ---------- ----------- ------------ ------------------ HSQL DROP_1 5 130 TABLE HSQL DROP_2 5 138 TABLE HSQL DROP_254 5 170 TABLE HSQL DROP_255 5 178 TABLE HSQL DROP_3 5 146 TABLE HSQL DROP_4 5 154 TABLE HSQL DROP_5 5 162 TABLE 8 rows selected. SQL> alter system flush shared_pool; alter system flush shared_pool; alter system flush buffer_cache; alter system flush buffer_cache;

4. 数据块结构解析

4.1 drop_1块结构解析

set dba 5,131 p ktbbh p kdbh d offset 36 count 12 BBED> p ktbbh struct ktbbh, 72 bytes @20 ub1 ktbbhtyp @20 0x01 (KDDBTDATA) union ktbbhsid, 4 bytes @24 ub4 ktbbhsg1 @24 0x00003618 ub4 ktbbhod1 @24 0x00003618 struct ktbbhcsc, 8 bytes @28 ub4 kscnbas @28 0x000b333f ub2 kscnwrp @32 0x0000 sb2 ktbbhict @36 -2046 ub1 ktbbhflg @38 0x32 (NONE) ub1 ktbbhfsl @39 0x00 ub4 ktbbhfnx @40 0x01400080 struct ktbbhitl[0], 24 bytes @44 struct ktbitxid, 8 bytes @44 ub2 kxidusn @44 0x0003 ub2 kxidslt @46 0x001e ub4 kxidsqn @48 0x0000013d struct ktbituba, 8 bytes @52 ub4 kubadba @52 0x00c009e4 ub2 kubaseq @56 0x0183 ub1 kubarec @58 0x53 ub2 ktbitflg @60 0x210d (KTBFUPB) union _ktbitun, 2 bytes @62 sb2 _ktbitfsc @62 0 ub2 _ktbitwrp @62 0x0000 ub4 ktbitbas @64 0x000b3351 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 sb2 _ktbitfsc @86 0 ub2 _ktbitwrp @86 0x0000 ub4 ktbitbas @88 0x00000000 BBED> p kdbh struct kdbh, 14 bytes @100 ub1 kdbhflag @100 0x00 (NONE) sb1 kdbhntab @101 1 sb2 kdbhnrow @102 269 sb2 kdbhfrre @104 -1 sb2 kdbhfsbo @106 556 sb2 kdbhfseo @108 1363 sb2 kdbhavsp @110 807 sb2 kdbhtosp @112 807 BBED> d offset 36 count 12 File: /data2/enmo/hsql01.dbf (5) Block: 131 Offsets: 36 to 47 Dba:0x01400083 ------------------------------------------------------------------------ 02f83200 80004001 03001e00 <32 bytes per line> BBED>

4.2 drop_2块结构解析

set dba 5,139 p ktbbh p kdbh d offset 36 count 12 BBED> p ktbbh struct ktbbh, 72 bytes @20 ub1 ktbbhtyp @20 0x01 (KDDBTDATA) union ktbbhsid, 4 bytes @24 ub4 ktbbhsg1 @24 0x00003619 ub4 ktbbhod1 @24 0x00003619 struct ktbbhcsc, 8 bytes @28 ub4 kscnbas @28 0x000b3340 ub2 kscnwrp @32 0x0000 sb2 ktbbhict @36 -2046 ub1 ktbbhflg @38 0x32 (NONE) ub1 ktbbhfsl @39 0x00 ub4 ktbbhfnx @40 0x01400088 struct ktbbhitl[0], 24 bytes @44 struct ktbitxid, 8 bytes @44 ub2 kxidusn @44 0x0003 ub2 kxidslt @46 0x001e ub4 kxidsqn @48 0x0000013d struct ktbituba, 8 bytes @52 ub4 kubadba @52 0x00c009e4 ub2 kubaseq @56 0x0183 ub1 kubarec @58 0x54 ub2 ktbitflg @60 0x210d (KTBFUPB) union _ktbitun, 2 bytes @62 sb2 _ktbitfsc @62 0 ub2 _ktbitwrp @62 0x0000 ub4 ktbitbas @64 0x000b3351 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 sb2 _ktbitfsc @86 0 ub2 _ktbitwrp @86 0x0000 ub4 ktbitbas @88 0x00000000 BBED> p kdbh struct kdbh, 14 bytes @100 ub1 kdbhflag @100 0x00 (NONE) sb1 kdbhntab @101 1 sb2 kdbhnrow @102 269 sb2 kdbhfrre @104 -1 sb2 kdbhfsbo @106 556 sb2 kdbhfseo @108 1363 sb2 kdbhavsp @110 807 sb2 kdbhtosp @112 807 BBED> d offset 36 count 12 File: /data2/enmo/hsql01.dbf (5) Block: 139 Offsets: 36 to 47 Dba:0x0140008b ------------------------------------------------------------------------ 02f83200 88004001 03001e00 <32 bytes per line> BBED>

4.3 drop_3块结构解析

set dba 5,147 p ktbbh p kdbh d offset 36 count 12 BBED> struct ktbbh, 96 bytes @20 ub1 ktbbhtyp @20 0x01 (KDDBTDATA) union ktbbhsid, 4 bytes @24 ub4 ktbbhsg1 @24 0x0000361a ub4 ktbbhod1 @24 0x0000361a struct ktbbhcsc, 8 bytes @28 ub4 kscnbas @28 0x000b3341 ub2 kscnwrp @32 0x0000 sb2 ktbbhict @36 -2045 ub1 ktbbhflg @38 0x32 (NONE) ub1 ktbbhfsl @39 0x00 ub4 ktbbhfnx @40 0x01400090 struct ktbbhitl[0], 24 bytes @44 struct ktbitxid, 8 bytes @44 ub2 kxidusn @44 0x0003 ub2 kxidslt @46 0x001e ub4 kxidsqn @48 0x0000013d struct ktbituba, 8 bytes @52 ub4 kubadba @52 0x00c009e4 ub2 kubaseq @56 0x0183 ub1 kubarec @58 0x47 ub2 ktbitflg @60 0x210c (KTBFUPB) union _ktbitun, 2 bytes @62 sb2 _ktbitfsc @62 0 ub2 _ktbitwrp @62 0x0000 ub4 ktbitbas @64 0x000b3351 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 sb2 _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 sb2 _ktbitfsc @110 0 ub2 _ktbitwrp @110 0x0000 ub4 ktbitbas @112 0x00000000 BBED> struct kdbh, 14 bytes @124 ub1 kdbhflag @124 0x00 (NONE) sb1 kdbhntab @125 1 sb2 kdbhnrow @126 268 sb2 kdbhfrre @128 -1 sb2 kdbhfsbo @130 554 sb2 kdbhfseo @132 1364 sb2 kdbhavsp @134 810 sb2 kdbhtosp @136 810 BBED> File: /data2/enmo/hsql01.dbf (5) Block: 147 Offsets: 36 to 47 Dba:0x01400093 ------------------------------------------------------------------------ 03f83200 90004001 03001e00 <32 bytes per line> BBED>

4.4 drop_4块结构解析

set dba 5,155 p ktbbh p kdbh d offset 36 count 12 BBED> struct ktbbh, 120 bytes @20 ub1 ktbbhtyp @20 0x01 (KDDBTDATA) union ktbbhsid, 4 bytes @24 ub4 ktbbhsg1 @24 0x0000361b ub4 ktbbhod1 @24 0x0000361b struct ktbbhcsc, 8 bytes @28 ub4 kscnbas @28 0x000b3342 ub2 kscnwrp @32 0x0000 sb2 ktbbhict @36 -2044 ub1 ktbbhflg @38 0x32 (NONE) ub1 ktbbhfsl @39 0x00 ub4 ktbbhfnx @40 0x01400098 struct ktbbhitl[0], 24 bytes @44 struct ktbitxid, 8 bytes @44 ub2 kxidusn @44 0x0003 ub2 kxidslt @46 0x001e ub4 kxidsqn @48 0x0000013d struct ktbituba, 8 bytes @52 ub4 kubadba @52 0x00c009e4 ub2 kubaseq @56 0x0183 ub1 kubarec @58 0x3a ub2 ktbitflg @60 0x210b (KTBFUPB) union _ktbitun, 2 bytes @62 sb2 _ktbitfsc @62 0 ub2 _ktbitwrp @62 0x0000 ub4 ktbitbas @64 0x000b3351 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 sb2 _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 sb2 _ktbitfsc @110 0 ub2 _ktbitwrp @110 0x0000 ub4 ktbitbas @112 0x00000000 struct ktbbhitl[3], 24 bytes @116 struct ktbitxid, 8 bytes @116 ub2 kxidusn @116 0x0000 ub2 kxidslt @118 0x0000 ub4 kxidsqn @120 0x00000000 struct ktbituba, 8 bytes @124 ub4 kubadba @124 0x00000000 ub2 kubaseq @128 0x0000 ub1 kubarec @130 0x00 ub2 ktbitflg @132 0x0000 (NONE) union _ktbitun, 2 bytes @134 sb2 _ktbitfsc @134 0 ub2 _ktbitwrp @134 0x0000 ub4 ktbitbas @136 0x00000000 BBED> struct kdbh, 14 bytes @148 ub1 kdbhflag @148 0x00 (NONE) sb1 kdbhntab @149 1 sb2 kdbhnrow @150 267 sb2 kdbhfrre @152 -1 sb2 kdbhfsbo @154 552 sb2 kdbhfseo @156 1365 sb2 kdbhavsp @158 813 sb2 kdbhtosp @160 813 BBED> File: /data2/enmo/hsql01.dbf (5) Block: 155 Offsets: 36 to 47 Dba:0x0140009b ------------------------------------------------------------------------ 04f83200 98004001 03001e00 <32 bytes per line> BBED>

4.5 drop_5块结构解析

set dba 5,163 p ktbbh p kdbh d offset 36 count 12 BBED> struct ktbbh, 144 bytes @20 ub1 ktbbhtyp @20 0x01 (KDDBTDATA) union ktbbhsid, 4 bytes @24 ub4 ktbbhsg1 @24 0x0000361c ub4 ktbbhod1 @24 0x0000361c struct ktbbhcsc, 8 bytes @28 ub4 kscnbas @28 0x000b3343 ub2 kscnwrp @32 0x0000 sb2 ktbbhict @36 -2043 ub1 ktbbhflg @38 0x32 (NONE) ub1 ktbbhfsl @39 0x00 ub4 ktbbhfnx @40 0x014000a0 struct ktbbhitl[0], 24 bytes @44 struct ktbitxid, 8 bytes @44 ub2 kxidusn @44 0x0003 ub2 kxidslt @46 0x001e ub4 kxidsqn @48 0x0000013d struct ktbituba, 8 bytes @52 ub4 kubadba @52 0x00c009e4 ub2 kubaseq @56 0x0183 ub1 kubarec @58 0x2d ub2 ktbitflg @60 0x210a (KTBFUPB) union _ktbitun, 2 bytes @62 sb2 _ktbitfsc @62 0 ub2 _ktbitwrp @62 0x0000 ub4 ktbitbas @64 0x000b3351 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 sb2 _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 sb2 _ktbitfsc @110 0 ub2 _ktbitwrp @110 0x0000 ub4 ktbitbas @112 0x00000000 struct ktbbhitl[3], 24 bytes @116 struct ktbitxid, 8 bytes @116 ub2 kxidusn @116 0x0000 ub2 kxidslt @118 0x0000 ub4 kxidsqn @120 0x00000000 struct ktbituba, 8 bytes @124 ub4 kubadba @124 0x00000000 ub2 kubaseq @128 0x0000 ub1 kubarec @130 0x00 ub2 ktbitflg @132 0x0000 (NONE) union _ktbitun, 2 bytes @134 sb2 _ktbitfsc @134 0 ub2 _ktbitwrp @134 0x0000 ub4 ktbitbas @136 0x00000000 struct ktbbhitl[4], 24 bytes @140 struct ktbitxid, 8 bytes @140 ub2 kxidusn @140 0x0000 ub2 kxidslt @142 0x0000 ub4 kxidsqn @144 0x00000000 struct ktbituba, 8 bytes @148 ub4 kubadba @148 0x00000000 ub2 kubaseq @152 0x0000 ub1 kubarec @154 0x00 ub2 ktbitflg @156 0x0000 (NONE) union _ktbitun, 2 bytes @158 sb2 _ktbitfsc @158 0 ub2 _ktbitwrp @158 0x0000 ub4 ktbitbas @160 0x00000000 BBED> struct kdbh, 14 bytes @172 ub1 kdbhflag @172 0x00 (NONE) sb1 kdbhntab @173 1 sb2 kdbhnrow @174 266 sb2 kdbhfrre @176 -1 sb2 kdbhfsbo @178 550 sb2 kdbhfseo @180 1366 sb2 kdbhavsp @182 816 sb2 kdbhtosp @184 816 BBED> File: /data2/enmo/hsql01.dbf (5) Block: 163 Offsets: 36 to 47 Dba:0x014000a3 ------------------------------------------------------------------------ 05f83200 a0004001 03001e00 <32 bytes per line> BBED>

4.6 drop_254块结构解析

set dba 5,171 p ktbbh p kdbh d offset 36 count 12 BBED> struct ktbbh, 144 bytes @20 ub1 ktbbhtyp @20 0x01 (KDDBTDATA) union ktbbhsid, 4 bytes @24 ub4 ktbbhsg1 @24 0x0000361c ub4 ktbbhod1 @24 0x0000361c struct ktbbhcsc, 8 bytes @28 ub4 kscnbas @28 0x000b3343 ub2 kscnwrp @32 0x0000 sb2 ktbbhict @36 -2043 ub1 ktbbhflg @38 0x32 (NONE) ub1 ktbbhfsl @39 0x00 ub4 ktbbhfnx @40 0x014000a0 struct ktbbhitl[0], 24 bytes @44 struct ktbitxid, 8 bytes @44 ub2 kxidusn @44 0x0003 ub2 kxidslt @46 0x001e ub4 kxidsqn @48 0x0000013d struct ktbituba, 8 bytes @52 ub4 kubadba @52 0x00c009e4 ub2 kubaseq @56 0x0183 ub1 kubarec @58 0x2d ub2 ktbitflg @60 0x210a (KTBFUPB) union _ktbitun, 2 bytes @62 sb2 _ktbitfsc @62 0 ub2 _ktbitwrp @62 0x0000 ub4 ktbitbas @64 0x000b3351 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 sb2 _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 sb2 _ktbitfsc @110 0 ub2 _ktbitwrp @110 0x0000 ub4 ktbitbas @112 0x00000000 struct ktbbhitl[3], 24 bytes @116 struct ktbitxid, 8 bytes @116 ub2 kxidusn @116 0x0000 ub2 kxidslt @118 0x0000 ub4 kxidsqn @120 0x00000000 struct ktbituba, 8 bytes @124 ub4 kubadba @124 0x00000000 ub2 kubaseq @128 0x0000 ub1 kubarec @130 0x00 ub2 ktbitflg @132 0x0000 (NONE) union _ktbitun, 2 bytes @134 sb2 _ktbitfsc @134 0 ub2 _ktbitwrp @134 0x0000 ub4 ktbitbas @136 0x00000000 struct ktbbhitl[167], 24 bytes @4052 struct ktbitxid, 8 bytes @4052 ub2 kxidusn @4052 0x0000 ub2 kxidslt @4054 0x0000 ub4 kxidsqn @4056 0x00000000 struct ktbituba, 8 bytes @4060 ub4 kubadba @4060 0x00000000 ub2 kubaseq @4064 0x0000 ub1 kubarec @4066 0x00 ub2 ktbitflg @4068 0x0000 (NONE) union _ktbitun, 2 bytes @4070 sb2 _ktbitfsc @4070 0 ub2 _ktbitwrp @4070 0x0000 ub4 ktbitbas @4072 0x00000000 struct ktbbhitl[168], 24 bytes @4076 struct ktbitxid, 8 bytes @4076 ub2 kxidusn @4076 0x0000 ub2 kxidslt @4078 0x0000 ub4 kxidsqn @4080 0x00000000 struct ktbituba, 8 bytes @4084 ub4 kubadba @4084 0x00000000 ub2 kubaseq @4088 0x0000 ub1 kubarec @4090 0x00 ub2 ktbitflg @4092 0x0000 (NONE) union _ktbitun, 2 bytes @4094 sb2 _ktbitfsc @4094 0 ub2 _ktbitwrp @4094 0x0000 ub4 ktbitbas @4096 0x00000000 BBED> struct kdbh, 14 bytes @4108 ub1 kdbhflag @4108 0x00 (NONE) sb1 kdbhntab @4109 1 sb2 kdbhnrow @4110 135 sb2 kdbhfrre @4112 -1 sb2 kdbhfsbo @4114 288 sb2 kdbhfseo @4116 705 sb2 kdbhavsp @4118 417 sb2 kdbhtosp @4120 417 BBED> File: /data2/enmo/hsql01.dbf (5) Block: 171 Offsets: 36 to 47 Dba:0x014000ab ------------------------------------------------------------------------ a9f83200 a8004001 03001e00 <32 bytes per line> BBED>

4.7 drop_255块结构解析

set dba 5,179 p ktbbh p kdbh d offset 36 count 12 BBED> struct ktbbh, 144 bytes @20 ub1 ktbbhtyp @20 0x01 (KDDBTDATA) union ktbbhsid, 4 bytes @24 ub4 ktbbhsg1 @24 0x0000361c ub4 ktbbhod1 @24 0x0000361c struct ktbbhcsc, 8 bytes @28 ub4 kscnbas @28 0x000b3343 ub2 kscnwrp @32 0x0000 sb2 ktbbhict @36 -2043 ub1 ktbbhflg @38 0x32 (NONE) ub1 ktbbhfsl @39 0x00 ub4 ktbbhfnx @40 0x014000a0 struct ktbbhitl[0], 24 bytes @44 struct ktbitxid, 8 bytes @44 ub2 kxidusn @44 0x0003 ub2 kxidslt @46 0x001e ub4 kxidsqn @48 0x0000013d struct ktbituba, 8 bytes @52 ub4 kubadba @52 0x00c009e4 ub2 kubaseq @56 0x0183 ub1 kubarec @58 0x2d ub2 ktbitflg @60 0x210a (KTBFUPB) union _ktbitun, 2 bytes @62 sb2 _ktbitfsc @62 0 ub2 _ktbitwrp @62 0x0000 ub4 ktbitbas @64 0x000b3351 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 sb2 _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 sb2 _ktbitfsc @110 0 ub2 _ktbitwrp @110 0x0000 ub4 ktbitbas @112 0x00000000 struct ktbbhitl[3], 24 bytes @116 struct ktbitxid, 8 bytes @116 ub2 kxidusn @116 0x0000 ub2 kxidslt @118 0x0000 ub4 kxidsqn @120 0x00000000 struct ktbituba, 8 bytes @124 ub4 kubadba @124 0x00000000 ub2 kubaseq @128 0x0000 ub1 kubarec @130 0x00 ub2 ktbitflg @132 0x0000 (NONE) union _ktbitun, 2 bytes @134 sb2 _ktbitfsc @134 0 ub2 _ktbitwrp @134 0x0000 ub4 ktbitbas @136 0x00000000 struct ktbbhitl[167], 24 bytes @4052 struct ktbitxid, 8 bytes @4052 ub2 kxidusn @4052 0x0000 ub2 kxidslt @4054 0x0000 ub4 kxidsqn @4056 0x00000000 struct ktbituba, 8 bytes @4060 ub4 kubadba @4060 0x00000000 ub2 kubaseq @4064 0x0000 ub1 kubarec @4066 0x00 ub2 ktbitflg @4068 0x0000 (NONE) union _ktbitun, 2 bytes @4070 sb2 _ktbitfsc @4070 0 ub2 _ktbitwrp @4070 0x0000 ub4 ktbitbas @4072 0x00000000 struct ktbbhitl[168], 24 bytes @4076 struct ktbitxid, 8 bytes @4076 ub2 kxidusn @4076 0x0000 ub2 kxidslt @4078 0x0000 ub4 kxidsqn @4080 0x00000000 struct ktbituba, 8 bytes @4084 ub4 kubadba @4084 0x00000000 ub2 kubaseq @4088 0x0000 ub1 kubarec @4090 0x00 ub2 ktbitflg @4092 0x0000 (NONE) union _ktbitun, 2 bytes @4094 sb2 _ktbitfsc @4094 0 ub2 _ktbitwrp @4094 0x0000 ub4 ktbitbas @4096 0x00000000 BBED> struct kdbh, 14 bytes @4108 ub1 kdbhflag @4108 0x00 (NONE) sb1 kdbhntab @4109 1 sb2 kdbhnrow @4110 135 sb2 kdbhfrre @4112 -1 sb2 kdbhfsbo @4114 288 sb2 kdbhfseo @4116 705 sb2 kdbhavsp @4118 417 sb2 kdbhtosp @4120 417 BBED> File: /data2/enmo/hsql01.dbf (5) Block: 179 Offsets: 36 to 47 Dba:0x014000b3 ------------------------------------------------------------------------ a9f83200 b0004001 03001e00 <32 bytes per line> BBED> --8192 40 + 4056=4096

4.8 16K blocksize

Block header dump: 0x01400043 Object id on Block? Y seg/obj: 0x35b2 csc: 0x00.330ea itc: 255 flg: E typ: 1 - DATA brn: 0 bdba: 0x1400040 ver: 0x01 opc: 0 inc: 0 exflg: 0 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0006.031.0000005a 0x00c02c98.0020.45 --U- 339 fsc 0x0000.000330ee 0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x04 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x05 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 ... 0xfc 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0xfd 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0xfe 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0xff 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

5. 内容总结

1. Oracle 8K blocksize 数据块初始 2个itl,8K blocksize 数据块最多169个itl,16K blocksize 数据块最多256个itl。 2. type_kcbh(offset 0): 0x06 表示为数据块,ktbbhtyp(offset 20): Typ: 1=DATA; 2=INDEX 3. offset 36: itl数量,40(before offset of ktbbhitl) + offset[offset36 * 24] + 8(ktbbh和kdbh存在8offset): kdbh结构的offset, 4. offsetOfKdbh+1: tab的数量,cluster表可能大于1,堆表等于1. 5. offsetOfKdbh+2: row的数量.
「喜欢文章,快来给作者赞赏墨值吧」
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论