數據塊內部偏移量的基本計算方法

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。