bbed 查看数据行
数据 行 存放
环境说明：
数据库版本：
Version 19.3.0.0.0 未升级补丁
用户为普通用户，表空间为users;
步骤：
我们分析前9行数据。
select rowid,OWNER from test where rownum<10;
ROWID OWNER
------------------ --------------------------------------------------------------------------------------------------------------------------------
AAASCdAAHAAAAFjAAA SYS
AAASCdAAHAAAAFjAAB SYS
AAASCdAAHAAAAFjAAC SYS
AAASCdAAHAAAAFjAAD SYS
AAASCdAAHAAAAFjAAE SYS
AAASCdAAHAAAAFjAAF SYS
AAASCdAAHAAAAFjAAG SYS
AAASCdAAHAAAAFjAAH SYS
AAASCdAAHAAAAFjAAI SYS
解析rowid 对应的dba地址
rowid 转换脚本
CREATE OR REPLACE FUNCTION GET_ROWID(L_ROWID IN VARCHAR2) RETURN VARCHAR2 IS
LS_MY_ROWID VARCHAR2(200);
ROWID_TYPE NUMBER;
OBJECT_NUMBER NUMBER;
RELATIVE_FNO NUMBER;
BLOCK_NUMBER NUMBER;
ROW_NUMBER NUMBER;
BEGIN
DBMS_ROWID.ROWID_INFO(L_ROWID,
ROWID_TYPE,
OBJECT_NUMBER,
RELATIVE_FNO,
BLOCK_NUMBER,
ROW_NUMBER);
LS_MY_ROWID := 'OBJECT# IS :' || TO_CHAR(OBJECT_NUMBER) || CHR(10) ||'RELATIVE_FNO IS :' || TO_CHAR(RELATIVE_FNO) || CHR(10) ||'BLOCK NUMBER IS :' || TO_CHAR(BLOCK_NUMBER) || CHR(10) ||'ROW NUMBER IS :' || TO_CHAR(ROW_NUMBER);
RETURN LS_MY_ROWID;
END GET_ROWID;
/
查询行位置：
select get_rowid('AAASFmAAHAAAAHnAAA') row_id from dual;
SQL> select get_rowid('AAASCdAAHAAAAFjAAA') row_id from dual;
ROW_ID
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
OBJECT# IS :73885
RELATIVE_FNO IS :7
BLOCK NUMBER IS :355
ROW NUMBER IS :0
SQL> select get_rowid('AAASCdAAHAAAAFjAAI') row_id from dual;
ROW_ID
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
OBJECT# IS :73885
RELATIVE_FNO IS :7
BLOCK NUMBER IS :355
ROW NUMBER IS :8
可以看到行的基本信息，数据文件7 的 355 块 row number 0--》8
alter system checkpoint;
然后通过bbed 操作：
选择相应的dba
BBED> set dba 7,355
DBA 0x01c00163 (29360483 7,355)
查看块基本信息
BBED> map /v
File: /u02/app/oracle/oradata/TEST/datafile/o1_mf_users_mnj4l868_.dbf (7)
Block: 355 Dba:0x01c00163
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
ub1 type_kcbh @0
ub1 frmt_kcbh @1
ub2 wrp2_kcbh @2
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, 96 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[3], 72 bytes @44
struct kdbh, 14 bytes @124
ub1 kdbhflag @124
sb1 kdbhntab @125
sb2 kdbhnrow @126
sb2 kdbhfrre @128
sb2 kdbhfsbo @130
sb2 kdbhfseo @132
sb2 kdbhavsp @134
sb2 kdbhtosp @136
struct kdbt[1], 4 bytes @138
sb2 kdbtoffs @138
sb2 kdbtnrow @140
sb2 kdbr[68] @142
ub1 freespace[816] @278
ub1 rowdata[7094] @1094
ub4 tailchk @8188
查看数据块存放了多少行数据
BBED> p kdbhnrow
sb2 kdbhnrow @126 68
查看行的信息
BBED> p kdbr
sb2 kdbr[0] @142 7950
sb2 kdbr[1] @144 7834
sb2 kdbr[2] @146 7721
sb2 kdbr[3] @148 7627
sb2 kdbr[4] @150 7511
sb2 kdbr[5] @152 7417
sb2 kdbr[6] @154 7323
sb2 kdbr[7] @156 7207
sb2 kdbr[8] @158 7113
sb2 kdbr[9] @160 7019
sb2 kdbr[10] @162 6915
sb2 kdbr[11] @164 6821
sb2 kdbr[12] @166 6726
sb2 kdbr[13] @168 6631
sb2 kdbr[14] @170 6525
sb2 kdbr[15] @172 6431
sb2 kdbr[16] @174 6315
sb2 kdbr[17] @176 6200
sb2 kdbr[18] @178 6078
sb2 kdbr[19] @180 5983
sb2 kdbr[20] @182 5888
sb2 kdbr[21] @184 5793
sb2 kdbr[22] @186 5678
sb2 kdbr[23] @188 5583
sb2 kdbr[24] @190 5475
sb2 kdbr[25] @192 5360
sb2 kdbr[26] @194 5265
sb2 kdbr[27] @196 5150
sb2 kdbr[28] @198 5056
sb2 kdbr[29] @200 4935
sb2 kdbr[30] @202 4841
sb2 kdbr[31] @204 4725
sb2 kdbr[32] @206 4619
sb2 kdbr[33] @208 4504
sb2 kdbr[34] @210 4399
sb2 kdbr[35] @212 4284
sb2 kdbr[36] @214 4169
sb2 kdbr[37] @216 4061
sb2 kdbr[38] @218 3960
sb2 kdbr[39] @220 3867
sb2 kdbr[40] @222 3752
sb2 kdbr[41] @224 3637
sb2 kdbr[42] @226 3521
sb2 kdbr[43] @228 3426
sb2 kdbr[44] @230 3331
sb2 kdbr[45] @232 3236
sb2 kdbr[46] @234 3134
sb2 kdbr[47] @236 3039
sb2 kdbr[48] @238 2945
sb2 kdbr[49] @240 2818
sb2 kdbr[50] @242 2723
sb2 kdbr[51] @244 2629
sb2 kdbr[52] @246 2535
sb2 kdbr[53] @248 2440
sb2 kdbr[54] @250 2347
sb2 kdbr[55] @252 2253
sb2 kdbr[56] @254 2158
sb2 kdbr[57] @256 2063
sb2 kdbr[58] @258 1943
sb2 kdbr[59] @260 1824
sb2 kdbr[60] @262 1726
sb2 kdbr[61] @264 1628
sb2 kdbr[62] @266 1515
sb2 kdbr[63] @268 1413
sb2 kdbr[64] @270 1294
sb2 kdbr[65] @272 1193
sb2 kdbr[66] @274 1072
sb2 kdbr[67] @276 970
查看某行信息
BBED> p *kdbr[0]
rowdata[6980]
-------------
ub1 rowdata[6980] @8074 0x2c
x /rc *kdbr[0]
x /rcccnncttcccccnccccccccnnnn *kdbr[0]
BBED> x /rcccnncttcccccnccccccccnnnn *kdbr[0]
rowdata[6980] @8074
-------------
flag@8074: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8075: 0x00
cols@8076: 22
col 0[3] @8077: SYS
col 1[3] @8081: TS$
col 2[0] @8085: *NULL*
col 3[2] @8086: 16
col 4[2] @8089: 6
col 5[5] @8092: TABLE
col 6[7] @8098: 17-APR-19
col 7[7] @8106: 17-APR-19
col 8[19] @8114: 2019-04-17:00:56:11
col 9[5] @8134: VALID
col 10[1] @8140: N
col 11[1] @8142: N
col 12[1] @8144: N
col 13[2] @8146: 1
col 14[0] @8149: *NULL*
col 15[13] @8150: METADATA LINK
col 16[0] @8164: *NULL*
col 17[1] @8165: Y
col 18[1] @8167: N
col 19[14] @8169: USING_NLS_COMP
col 20[1] @8184: N
col 21[1] @8186: N
set serveroutput on
exec system.print_table('select * from test where rownum=1');
SQL> set serveroutput on
exec system.print_table('select * from test where rownum=1');
SQL>
OWNER : SYS
OBJECT_NAME : TS$
SUBOBJECT_NAME :
OBJECT_ID : 16
DATA_OBJECT_ID : 6
OBJECT_TYPE : TABLE
CREATED : 2019-04-17 00:56:11
LAST_DDL_TIME : 2019-04-17 01:46:57
TIMESTAMP : 2019-04-17:00:56:11
STATUS : VALID
TEMPORARY : N
GENERATED : N
SECONDARY : N
NAMESPACE : 1
EDITION_NAME :
SHARING : METADATA LINK
EDITIONABLE :
ORACLE_MAINTAINED : Y
APPLICATION : N
DEFAULT_COLLATION : USING_NLS_COMP
DUPLICATED : N
SHARDED : N
CREATED_APPID :
CREATED_VSNID :
MODIFIED_APPID :
MODIFIED_VSNID :
BBED> dump /v count 512
File: /u02/app/oracle/oradata/TEST/datafile/o1_mf_users_mnj4l868_.dbf (7)
Block: 355 Offsets: 8074 to 8191 Dba:0x01c00163
-------------------------------------------------------
2c001603 53595303 545324ff 02c11102 l ,...SYS.TS$...
c1070554 41424c45 07787704 1101390c l .TABLE.xw...9. --> 05是列宽 5441424c45 是TABLE 07是列宽 7877041101390c 是2019-04-17 00:56:11
07787704 11022f3a 13323031 392d3034 l .xw.../:.2019-04 --》07列宽 78770411022f3a 是 2019-04-17 01:46:57 13是列宽（10进制19） 323031392d30342d31373a30303a35363a3131 是2019-04-17:00:56:11
2d31373a 30303a35 363a3131 0556414c l -17:00:56:11.VAL --》05是列宽 56414c4944是VALID 01是列宽 4e是N 重复了3次
4944014e 014e014e 02c102ff 0d4d4554 l ID.N.N.N...MET 01是列宽 4e是N 重复了3次 02 是列宽 c102是 1 ff 是空值 0d是列宽 （13） 4d45544144415441204c494e4b对应的值是METADATA LINK
41444154 41204c49 4e4bff01 59014e0e l ADATA LINK..Y.N.
5553494e 475f4e4c 535f434f 4d50014e l USING_NLS_COMP.N
014e0206 43dc l .N..C
<16 bytes per line>
/*******************
flag的标志位：
ROW_CLUSTER_KEY = 0x80; KDRHFK
ROW_CTABLE_NUMBER = 0x40; KDRHFC
ROW_HEAD_PIECE = 0x20; KDRHFH
ROW_DELETED_ROW = 0x10; KDRHFD
ROW_FIRST_PIECE = 0x08; KDRHFF
ROW_LAST_PIECE = 0x04; KDRHFL
ROW_FROM_PREVIOUS = 0x02; KDRHFP
ROW_CONTINUE_NEXT = 0x01; KDRHFN
*******************/
每一条row piece的头部都有flag、locks、cols(cc)三个标志位
2c 表示flag,00 表示lock ,16 表示 cols(转换成10进制是22). 后面这个 03 是字段长度
535953 代表的是SYS 03是字段长度 545324 是 TS$
ff 代表的是空列
02 是数字列的列宽 c111 是 16
02 是数字列的列宽 c107 是 6
/*******************
SQL> SELECT RAWTOHEX(16) FROM DUAL;
C111
SQL> SELECT RAWTOHEX(6) FROM DUAL;
C107
SQL> select FUN_DISPLAY_RAW_LHR('c111','NUMBER') LOW_VALUE1 from dual;
LOW_VALUE1
--------------------------------------------------------------------------------
16
*******************/
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/69924215/viewspace-3054898/ ，如需转载，请注明出处，否则将追究法律责任。
