Sundaramurthy Blog

June 9, 2010

How to calculate oracle row size using block dump?

Filed under: Oracle Block,Oracle internals — sundar5 @ 7:39 pm

Row bytes calculation:

data block internal structure

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
b2 ktbbhict                             @36
ub1 ktbbhflg                            @38
ub1 ktbbhfsl                            @39
ub4 ktbbhfnx                            @40
struct ktbbhitl[2], 48 bytes            @44

ITL STRUCTURE
————–
struct ktbbhitl[0], 24 bytes                @44
struct ktbitxid, 8 bytes                 @44
ub2 kxidusn                           @44       0x0001
ub2 kxidslt                           @46       0x000f
ub4 kxidsqn                           @48       0x000000da
struct ktbituba, 8 bytes                 @52
ub4 kubadba                           @52       0x008008d9
ub2 kubaseq                           @56       0x0083
ub1 kubarec                           @58       0x12
ub2 ktbitflg                             @60       0x2003 (KTBFUPB)
union _ktbitun, 2 bytes                  @62
b2 _ktbitfsc                          @62       0
ub2 _ktbitwrp                         @62       0x0000
ub4 ktbitbas                             @64       0x000bd7c1
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 kdbh, 14 bytes                   @100
ub1 kdbhflag                            @100
b1 kdbhntab                             @101
b2 kdbhnrow                             @102
sb2 kdbhfrre                            @104
sb2 kdbhfsbo                            @106
sb2 kdbhfseo                            @108
b2 kdbhavsp                             @110
b2 kdbhtosp                             @112
struct kdbt[1], 4 bytes                 @114
b2 kdbtoffs                             @114
b2 kdbtnrow                             @116
sb2 kdbr[389]                           @118
ub1 freespace[1052]                     @896
ub1 rowdata[6240]                       @1948
ub4 tailchk                             @8188

row directory
kdbr
sb2 kdbr[0]                              @118      4107
sb2 kdbr[1]                              @120      4119
sb2 kdbr[2]                              @122      4133

row data:
rowdata[2285]                               @4233

————-

flag@4233: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@4234: 0x00

cols@4235:    2

col    0[2] @4236: 28

col    1[4] @4239: CON$

rowdata[2296]                               @4244

————-

flag@4244: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@4245: 0x00

cols@4246:    2

col    0[2] @4247: 15

col    1[5] @4250: UNDO$

rowdata[2308]                               @4256

————-

flag@4256: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@4257: 0x00

cols@4258:    2

col    0[2] @4259: 29

col    1[7] @4262: C_COBJ#

3 bytes for row header
1 bytes for each colume size
3 bytes for column size, if colume value  is more than 254 bytes(xfe xxxx)
x bytes for column data
1 bytes if column value is null

dump the block using  oracle command and locate the rows in the bottom of the block

Example 1

tab 0, row 0, @0x1f88
tl: 16 fb: –H-FL– lb: 0x0 cc: 2
col 0: [ 2] c2 02
col 1: [ 9] 54 45 53 54 44 41 54 41 31

16 = 3 + 1 + 2 +1 + 9

Example 2

tab 0, row 0, @0x1f89
tl: 15 fb: –H-FL– lb: 0x0 cc: 2
col 0: [ 1] 80
col 1: [ 9] 54 45 53 54 44 41 54 41 30

15 = 3+1 + 1 + 1 + 9

Example 3

tab 0, row 1, @0x1f78
tl: 17 fb: –H-FL– lb: 0x1 cc: 7
col 0: [ 2] 31 31
col 1: *NULL*
col 2: *NULL*
col 3: *NULL*
col 4: *NULL*
col 5: *NULL*
col 6: [ 5] 46 49 52 53 54

17 =  3 +1 + 2 + 1 +1 + 1 + 1 +1+ 1+5

Example 4

tab 0, row 4, @0x1f4c
tl: 6 fb: –H-FL– lb: 0x2 cc: 1
col 0: [ 2] 34 34

6 = 3+1+2

Example 5

column size is more than 254 bytes

tab 0, row 0, @0x1e63
tl: 309 fb: –H-FL– lb: 0x0  cc: 2
col  0: [ 2]  c1 0b
col  1: [300]
78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78

309 = 3 + 1 + 2 +3 + 300

Hex dump

79797979 7979792c 000202c1 0bfe2c01 l yyyyyyy,…Á.þ,.
78787878 78787878 78787878 78787878 l xxxxxxxxxxxxxxxx
78787878 78787878 78787878 78787878 l xxxxxxxxxxxxxxxx
78787878 78787878 78787878 78787878 l xxxxxxxxxxxxxxxx
78787878 78787878 78787878 78787878 l xxxxxxxxxxxxxxxx
78787878 78787878 78787878 78787878 l xxxxxxxxxxxxxxxx
78787878 78787878 78787878 78787878 l xxxxxxxxxxxxxxxx
78787878 78787878 78787878 78787878 l xxxxxxxxxxxxxxxx
78787878 78787878 78787878 78787878 l xxxxxxxxxxxxxxxx
78787878 78787878 78787878 78787878 l xxxxxxxxxxxxxxxx
78787878 78787878 78787878 78787878 l xxxxxxxxxxxxxxxx
78787878 78787878 78787878 78787878 l xxxxxxxxxxxxxxxx
78787878 78787878 78787878 78787878 l xxxxxxxxxxxxxxxx
78787878 78787878 78787878 78787878 l xxxxxxxxxxxxxxxx
78787878 78787878 78787878 78787878 l xxxxxxxxxxxxxxxx
78787878 78787878 78787878 78787878 l xxxxxxxxxxxxxxxx
78787878 78787878 78787878 78787878 l xxxxxxxxxxxxxxxx
78787878 78787878 78787878 78787878 l xxxxxxxxxxxxxxxx
78787878 78787878 78787878 78787878 l xxxxxxxxxxxxxxxx
78787878 78787878 78787878 0206d607 l xxxxxxxxxxxx..Ö.

02c1 0bfe2c01

02 bytes first column size
c1 0b = first column value
fe=  Indicator above 254 bytes column size (second column more than 254 bytes)
012c = 300 bytes second column size

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: