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

BASE的计算方法为：
对于ASSM：76+（itc-1) * 24= 52 + itc * 24
对于MSSM：68+（itc-1) * 24= 44 + itc * 24

点击(此处)折叠或打开

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

2. 
   

3. COMPONEN TYPE DESCRIPTION TYPE_SIZE
   

4. -------- -------- -------------------------------- ----------
   

5. KCB KCBH BLOCK COMMON HEADER 20
   

6. KTB KTBIT TRANSACTION VARIABLE HEADER 24
   

7. KTB KTBBH TRANSACTION FIXED HEADER 48
   

8. KTB KTBBH_BS TRANSACTION BLOCK BITMAP SEGMENT 8

1、我们先对ASSM做测试

点击(此处)折叠或打开

1. gyj@ZMDB> select * from v$version;
   

2. 
   

3. BANNER
   

4. --------------------------------------------------------------------------------
   

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

6. 
   

7. gyj@ZMDB> create tablespace assm datafile '/u01/app/oracle/oradata/zmdb/assm01.dbf' size 50M;
   

8. 
   

9. Tablespace created.
   

10. 
    

11. gyj@ZMDB> create table gyj_t5(id int,name varchar2(100)) tablespace assm;
    

12. 
    

13. Table created.
    

14. 
    

15. gyj@ZMDB> insert into gyj_t5 values(1,'AAAAA');
    

16. 
    

17. 1 row created.
    

18. 
    

19. gyj@ZMDB> insert into gyj_t5 values(2,'BBBBB');
    

20. 
    

21. 1 row created.
    

22. 
    

23. gyj@ZMDB> insert into gyj_t5 values(3,'CCCCC');
    

24. 
    

25. 1 row created.
    

26. 
    

27. gyj@ZMDB> COMMIT;
    

28. 
    

29. Commit complete.
    

30. 
    

31. gyj@ZMDB> alter system flush buffer_cache;
    

32. 
    

33. System altered.
    

34. 
    

35. gyj@ZMDB> select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block#,id,name from gyj_t5;
    

36. 
    

37. FILE# BLOCK# ID NAME
    

38. ---------- ---------- ---------- ----------
    

39. 10 135 1 AAAAA
    

40. 10 135 2 BBBBB
    

41. 10 135 3 CCCCC
    

42. 
    

43. 
    

44. BBED> set file 10 block 135
    

45. FILE# 10
    

46. BLOCK# 135
    

47. 
    

48. BBED> p kdbr[0]
    

49. sb2 kdbr[0] @118 8076
    

50. 
    

51. BBED> p *kdbr[0]
    

52. rowdata[24]
    

53. -----------
    

54. ub1 rowdata[24] @8176 0x2c
    

55. 
    

56. BBED> x /rnc
    

57. rowdata[24] @8176
    

58. -----------
    

59. flag@8176: 0x2c (KDRHFL, KDRHFF, KDRHFH)
    

60. lock@8177: 0x01
    

61. cols@8178: 2
    

62. 
    

63. col 0[2] @8179: 1
    

64. col 1[5] @8182: AAAAA
    

65. 
    

66. BBED> p ktbbhict
    

67. sb2 ktbbhict @36 2
    

68. 
    

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

2、我们对MSSM做测试

点击(此处)折叠或打开

1. gyj@ZMDB> create tablespace mssm datafile '/u01/app/oracle/oradata/zmdb/mssm01.dbf' size 50M segment space management manual;
   

2. 
   

3. Tablespace created.
   

4. 
   

5. 
   

6. gyj@ZMDB> create table gyj_mssm(id int,name varchar2(100)) tablespace mssm;
   

7. 
   

8. Table created.
   

9. 
   

10. gyj@ZMDB> insert into gyj_mssm values(4,'DDDDD');
    

11. 
    

12. 1 row created.
    

13. 
    

14. gyj@ZMDB> insert into gyj_mssm values(5,'EEEEE');
    

15. 
    

16. 1 row created.
    

17. 
    

18. gyj@ZMDB> insert into gyj_mssm values(6,'FFFFF');
    

19. 
    

20. 1 row created.
    

21. 
    

22. gyj@ZMDB> commit;
    

23. 
    

24. Commit complete.
    

25. 
    

26. 
    

27. gyj@ZMDB> col name for a20
    

28. gyj@ZMDB> select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block#,id,name from gyj_mssm;
    

29. 
    

30. FILE# BLOCK# ID NAME
    

31. ---------- ---------- ---------- --------------------
    

32. 11 129 4 DDDDD
    

33. 11 129 5 EEEEE
    

34. 11 129 6 FFFFF
    

35. 
    

36. BBED> set file 11 block 129
    

37. FILE# 11
    

38. BLOCK# 129
    

39. 
    

40. BBED> p kdbr[0]
    

41. sb2 kdbr[0] @110 8084
    

42. 
    

43. BBED> p *kdbr[0]
    

44. rowdata[24]
    

45. -----------
    

46. ub1 rowdata[24] @8176 0x2c
    

47. 
    

48. BBED> x /rnc
    

49. rowdata[24] @8176
    

50. -----------
    

51. flag@8176: 0x2c (KDRHFL, KDRHFF, KDRHFH)
    

52. lock@8177: 0x01
    

53. cols@8178: 2
    

54. 
    

55. col 0[2] @8179: 4
    

56. col 1[5] @8182: DDDDD
    

57. 
    

58. BBED> p ktbbhict
    

59. sb2 ktbbhict @36 2
    

60. 
    

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

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

点击(此处)折叠或打开

1. ************MSSM
   

2. BBED> set file 11 block 129
   

3. FILE# 11
   

4. BLOCK# 129
   

5. 
   

6. BBED> map /v
   

7. File: /u01/app/oracle/oradata/zmdb/mssm01.dbf (11)
   

8. Block: 129 Dba:0x02c00081
   

9. ------------------------------------------------------------
   

10. KTB Data Block (Table/Cluster)
    

11. 
    

12. struct kcbh, 20 bytes @0
    

13. ub1 type_kcbh @0
    

14. ub1 frmt_kcbh @1
    

15. ub1 spare1_kcbh @2
    

16. ub1 spare2_kcbh @3
    

17. ub4 rdba_kcbh @4
    

18. ub4 bas_kcbh @8
    

19. ub2 wrp_kcbh @12
    

20. ub1 seq_kcbh @14
    

21. ub1 flg_kcbh @15
    

22. ub2 chkval_kcbh @16
    

23. ub2 spare3_kcbh @18
    

24. 
    

25. struct ktbbh, 72 bytes @20
    

26. ub1 ktbbhtyp @20
    

27. union ktbbhsid, 4 bytes @24
    

28. struct ktbbhcsc, 8 bytes @28
    

29. sb2 ktbbhict @36
    

30. ub1 ktbbhflg @38
    

31. ub1 ktbbhfsl @39
    

32. ub4 ktbbhfnx @40
    

33. struct ktbbhitl[2], 48 bytes @44
    

34. 
    

35. struct kdbh, 14 bytes @92
    

36. ub1 kdbhflag @92
    

37. sb1 kdbhntab @93
    

38. sb2 kdbhnrow @94
    

39. sb2 kdbhfrre @96
    

40. sb2 kdbhfsbo @98
    

41. sb2 kdbhfseo @100
    

42. sb2 kdbhavsp @102
    

43. sb2 kdbhtosp @104
    

44. 
    

45. struct kdbt[1], 4 bytes @106
    

46. sb2 kdbtoffs @106
    

47. sb2 kdbtnrow @108
    

48. 
    

49. sb2 kdbr[3] @110
    

50. 
    

51. ub1 freespace[8036] @116
    

52. 
    

53. ub1 rowdata[36] @8152
    

54. 
    

55. ub4 tailchk @8188
    

56. 
    

57. *****************ASSM
    

58. BBED> set file 10 block 135
    

59. FILE# 10
    

60. BLOCK# 135
    

61. 
    

62. File: /u01/app/oracle/oradata/zmdb/assm01.dbf (10)
    

63. Block: 141 Dba:0x0280008d
    

64. ------------------------------------------------------------
    

65. KTB Data Block (Table/Cluster)
    

66. 
    

67. struct kcbh, 20 bytes @0
    

68. ub1 type_kcbh @0
    

69. ub1 frmt_kcbh @1
    

70. ub1 spare1_kcbh @2
    

71. ub1 spare2_kcbh @3
    

72. ub4 rdba_kcbh @4
    

73. ub4 bas_kcbh @8
    

74. ub2 wrp_kcbh @12
    

75. ub1 seq_kcbh @14
    

76. ub1 flg_kcbh @15
    

77. ub2 chkval_kcbh @16
    

78. ub2 spare3_kcbh @18
    

79. 
    

80. struct ktbbh, 72 bytes @20
    

81. ub1 ktbbhtyp @20
    

82. union ktbbhsid, 4 bytes @24
    

83. struct ktbbhcsc, 8 bytes @28
    

84. sb2 ktbbhict @36
    

85. ub1 ktbbhflg @38
    

86. ub1 ktbbhfsl @39
    

87. ub4 ktbbhfnx @40
    

88. struct ktbbhitl[2], 48 bytes @44
    

89. 
    

90. struct kdbh, 14 bytes @100
    

91. ub1 kdbhflag @100
    

92. sb1 kdbhntab @101
    

93. sb2 kdbhnrow @102
    

94. sb2 kdbhfrre @104
    

95. sb2 kdbhfsbo @106
    

96. sb2 kdbhfseo @108
    

97. sb2 kdbhavsp @110
    

98. sb2 kdbhtosp @112
    

99. 
    

100. struct kdbt[1], 4 bytes @114
     

101. sb2 kdbtoffs @114
     

102. sb2 kdbtnrow @116
     

103. 
     

104. sb2 kdbr[3] @118
     

105. 
     

106. ub1 freespace[8028] @124
     

107. 
     

108. ub1 rowdata[36] @8152
     

109. 
     

110. ub4 tailchk @8188

对比
-----MSSM
struct ktbbhitl[2], 48 bytes @44

struct kdbh, 14 bytes @92

---ASSM
struct ktbbhitl[2], 48 bytes @44

struct kdbh, 14 bytes @100

@92---->@100 kdbh偏移量发生了变化，增加了8个byte。