Basic calculation method of internal offset of data block
The BASE is calculated as follows:
For ASSM:76+ (itc-1) * 24 = 52 + itc * 24
For MSSM:68+ (itc-1) * 24 = 44 + itc * 24
Click (here) to collapse or open
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. Let's test ASSM first.
Click (here) to collapse or open
Gyj@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 size
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 recording AAAAAAA')
1 row created.
Gyj@ZMDB > insert into gyj_t5 values (2 BBBBBB')
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-807676 + (itc-1) * 24 = 76 + (2-1) * 240100
2. We test MSSM.
Click (here) to collapse or open
Gyj@ZMDB > create tablespace mssm datafile'/ u01 size 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 (4ddd')
1 row created.
Gyj@ZMDB > insert into gyj_mssm values (5 refine EEEE')
1 row created.
Gyj@ZMDB > insert into gyj_mssm values (6 FFFFFF')
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. Why is ASSM 8 bytes more than MSSM
Click (here) to collapse or open
* 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
Contrast
-MSSM
Struct ktbbhitl [2], 48 bytes @ 44
Struct kdbh, 14 bytes @ 92
-ASSM
Struct ktbbhitl [2], 48 bytes @ 44
Struct kdbh, 14 bytes @ 100
@ 92Mutual-> @ 100 kdbh offset has changed, adding 8 byte.
