查看block dump对应的实际值
第一步：登录数据库创建测试表
SQL> conn zc/zc
Connected.
SQL>
SQL>
SQL> create table test(id number,name varchar2(20),ctime date);
Table created.
SQL> insert into test values(5674,'zhangchao',sysdate);
1 row created.
SQL> select * from test;
ID NAME CTIME
---------- -------------------- ---------
5674 zhangchao 20-NOV-24
SQL> alter session set nls_date_format = 'yyyy/mm/dd hh24:mi:ss' ;
Session altered.
SQL> select * from test;
ID NAME CTIME
---------- -------------------- -------------------
5674 zhangchao 2024/11/20 17:47:29
SQL> select dbms_rowid.ROWID_RELATIVE_FNO(rowid) fno,dbms_rowid.rowid_block_number(rowid) blkno from test;
FNO BLKNO
---------- ----------
7 347
第二步：根据块id dump 改数据块
SQL> alter system dump datafile 7 block 347;
System altered.
Trace file /u02/app/oracle/diag/rdbms/gbk/gbk/trace/gbk_ora_7952.trc
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0
Build label: RDBMS_19.22.0.0.0DBRU_LINUX.X64_231229
ORACLE_HOME: /u02/app/oracle/product/19.9.0/db_1
System name: Linux
Node name: localhost.localdomain
Release: 4.18.0-193.el8.x86_64
Version: #1 SMP Fri Mar 27 14:35:58 UTC 2020
Machine: x86_64
CLID: P
Instance name: gbk
Redo thread mounted by this instance: 1
Oracle process number: 50
Unix process pid: 7952, image: oracle@localhost.localdomain (TNS V1-V3)
*** 2024-11-20T17:49:47.105940-08:00
*** SESSION ID:(462.62967) 2024-11-20T17:49:47.105974-08:00
*** CLIENT ID:() 2024-11-20T17:49:47.105995-08:00
*** SERVICE NAME:(SYS$USERS) 2024-11-20T17:49:47.106015-08:00
*** MODULE NAME:(SQL*Plus) 2024-11-20T17:49:47.106036-08:00
*** ACTION NAME:() 2024-11-20T17:49:47.106056-08:00
*** CLIENT DRIVER:(SQL*PLUS) 2024-11-20T17:49:47.106076-08:00
Start dump data blocks tsn: 4 file#:7 minblk 347 maxblk 347
Block dump from cache:
Dump of buffer cache at level 3 for pdb=0 tsn=4 rdba=29360475
BH (0x9a3e5b18) file#: 7 rdba: 0x01c0015b (7/347) class: 1 ba: 0x9a1c2000
set: 3 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,0
dbwrid: 0 obj: 74213 objn: 74213 tsn: [0/4] afn: 7 hint: f
hash: [0x6d3cdfc0,0x6d3cdfc0] lru: [0x9a3e5d68,0x9a3e5a98]
obj-flags: object_ckpt_list
ckptq: [0x9a3e5848,0x9bfe7030] fileq: [0x9a3e5858,0x9a3e67d0]
objq: [0x9a3e6768,0x9a3e5ac0] objaq: [0x9a3e6778,0x9a3e5ad0]
st: XCURRENT md: NULL fpin: 'ktspbwh2: ktspfmdb' fscn: 0x2162e2 tch: 4
flags: buffer_dirty
LRBA: [0xa.5b2c0.0] LSCN: [0x2162e2] HSCN: [0x2162e2] HSUB: [1]
Printing buffer operation history (latest change first): (cnt 4)
01. sid:14 L464:chg1_mn:bic:FMS 02. sid:14 L778:chg1_mn:bis:FMS
03. sid:14 L353:gcur:set:MEXCL 04. sid:14 L464:chg1_mn:bic:FMS
05. sid:14 L614:chg1_mn:bis:FBD 06. sid:14 L922:klbc:sw:cq
07. sid:14 L778:chg1_mn:bis:FMS 08. sid:14 L004:new:acq:pin
09. sid:14 L229:bnew:bic:FEN 10. sid:14 L143:new:mk:EXCL
11. sid:14 L177:kcbnew:bic:FSI 12. sid:14 L982:new:bic:FBS
13. sid:14 L176:kcbnew:bic:FSQ 14. sid:14 L122:zgb:set:st
15. sid:14 L830:olq1:clr:WRT+CKT 16. sid:14 L951:zgb:lnk:objq
buffer tsn: 4 rdba: 0x01c0015b (7/347)
scn: 0x2162e2 seq: 0x03 flg: 0x00 tail: 0x62e20603
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x000000009A1C2000 to 0x000000009A1C4000
09A1C2000 0000A206 01C0015B 002162E2 00030000 [....[....b!.....]
09A1C2010 00000000 00000001 000121E5 002162E2 [.........!...b!.]
09A1C2020 00008000 00320002 01C00158 000B0003 [......2.X.......]
09A1C2030 000003F1 01000305 000D00E6 00000001 [................]
09A1C2040 00000000 00000000 00000000 00000000 [................]
Repeat 1 times
09A1C2060 00000000 00010100 0014FFFF 1F6B1F7F [..............k.]
09A1C2070 00001F6B 1F7F0001 00000000 00000000 [k...............]
09A1C2080 00000000 00000000 00000000 00000000 [................]
Repeat 501 times
09A1C3FE0 2C000000 C2030301 7A094B39 676E6168 [...,....9K.zhang]
09A1C3FF0 6F616863 0B7C7807 1E301214 62E20603 [chao.x|...0....b]
Block header dump: 0x01c0015b
Object id on Block? Y
seg/obj: 0x121e5 csc: 0x00000000002162e2 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1c00158 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0003.00b.000003f1 0x01000305.00e6.0d ---- 1 fsc 0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x01c0015b
data_block_dump,data header at 0x9a1c2064
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x9a1c2064
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f7f
avsp=0x1f6b
tosp=0x1f6b
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f7f
block_row_dump:
tab 0, row 0, @0x1f7f
tl: 25 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 3] c2 39 4b
col 1: [ 9] 7a 68 61 6e 67 63 68 61 6f
col 2: [ 7] 78 7c 0b 14 12 30 1e
end_of_block_dump
Dump of buffer cache for pdb 0 tsn 4 rdba 0x1c0015b at level 2 done.
Block dump from disk:
buffer tsn: 4 rdba: 0x01c0015b (7/347)
scn: 0x0 seq: 0x01 flg: 0x05 tail: 0x00000001
frmt: 0x02 chkval: 0xa79b type: 0x00=unknown
Hex dump of block: st=0, typ_found=0
Dump of memory from 0x00007F91FBD83000 to 0x00007F91FBD85000
7F91FBD83000 0000A200 01C0015B 00000000 05010000 [....[...........]
7F91FBD83010 0000A79B 00000000 00000000 00000000 [................]
7F91FBD83020 00000000 00000000 00000000 00000000 [................]
Repeat 508 times
7F91FBD84FF0 00000000 00000000 00000000 00000001 [................]
Dump of memory from 0x00007F91FBD83014 to 0x00007F91FBD84FFC
7F91FBD83010 00000000 00000000 00000000 [............]
7F91FBD83020 00000000 00000000 00000000 00000000 [................]
Repeat 508 times
7F91FBD84FF0 00000000 00000000 00000000 [............]
End dump data blocks tsn: 4 file#: 7 minblk 347 maxblk 347
121e5 -->10进制 74213
–分析ump
根据seg/obj: 0x121e5 我们先确认是这个对象
SQL> select object_id from dba_objects where owner='ZC' and object_name='TEST';
OBJECT_ID
----------
74213
SQL> select to_number('121e5','xxxxxxx') from dual;
TO_NUMBER('121E5','XXXXXXX')
----------------------------
74213
select objd,ts#,status from v$bh where file#=7 and block#=347;
OBJD TS# STATUS
---------- ---------- ----------
74213 4 xcur
根据bdba: 0x01c0015b 也可以确认到那个块
variable dba varchar2(30)
exec :dba := dbms_utility.make_data_block_address(7,347);
SQL> variable dba varchar2(30)
exec :dba := dbms_utility.make_data_block_address(7,347);
SQL>
PL/SQL procedure successfully completed.
SQL>
SQL> print dba
DBA
--------------------------------------------------------------------------------
29360475
SQL> select to_char(29360475,'xxxxxxxxxxx') from dual;
TO_CHAR(2936
------------
1c0015b
可以看到正反向验证都没有问题
dump中的数据情况
col 0: [ 3] c2 39 4b
col 1: [ 9] 7a 68 61 6e 67 63 68 61 6f
col 2: [ 7] 78 7c 0b 14 12 30 1e
第三步：bbed 分析
通过bbed 查看数据情况
BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0] @8163 0x2c
BBED> x /r
rowdata[0] @8163
----------
flag@8163: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8164: 0x01
cols@8165: 3
col 0[3] @8166: 0xc2 0x39 0x4b
col 1[9] @8170: 0x7a 0x68 0x61 0x6e 0x67 0x63 0x68 0x61 0x6f
col 2[7] @8180: 0x78 0x7c 0x0b 0x14 0x12 0x30 0x1e
BBED> x /rncd
rowdata[0] @8163
----------
flag@8163: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8164: 0x01
cols@8165: 3
col 0[3] @8166: 5674
col 1[9] @8170: zhangchao
col 2[7] @8180: 120 124 11 20 18 48 30
BBED> x /rnct
rowdata[0] @8163
----------
flag@8163: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8164: 0x01
cols@8165: 3
col 0[3] @8166: 5674
col 1[9] @8170: zhangchao
col 2[7] @8180: 20-NOV-24
第四步： 转换数据
SQL> select dbms_stats.CONVERT_RAW_TO_VARCHAR2('7a68616e676368616f') FROM dual;
DBMS_STATS.CONVERT_RAW_TO_VARCHAR2('7A68616E676368616F')
--------------------------------------------------------------------------------
zhangchao
SQL> select dbms_stats.CONVERT_RAW_TO_DATE('787c0b1412301e') from dual ;
DBMS_STATS.CONVER
-----------------
20241120 17:47:29
SQL> select dbms_stats.CONVERT_RAW_TO_NUMBER('c2394b') FROM dual;
DBMS_STATS.CONVERT_RAW_TO_NUMBER('C2394B')
------------------------------------------
5674
SQL> select * from test;
ID NAME CTIME
---------- -------------------- ---------
5674 zhangchao 20-NOV-24
12c 开始 有了下面的函数
DBMS_STATS.CONVERT_RAW_TO_VARCHAR2
DBMS_STATS.CONVERT_RAW_TO_DATE
DBMS_STATS.CONVERT_RAW_TO_NUMBER
DBMS_STATS.CONVERT_RAW_TO_BIN_FLOAT
DBMS_STATS.CONVERT_RAW_TO_BIN_DOUBLE
DBMS_STATS.CONVERT_RAW_TO_NVARCHAR
DBMS_STATS.CONVERT_RAW_TO_ROWID
11g 以下版本
create or replace function stats_raw_to_date (p_in raw) return date is
v_date date;
v_char varchar2(25);
begin
dbms_stats.CONVERT_RAW_VALUE(p_in, v_date);
return v_date;
exception
when others then return null;
end;
/
create or replace function zero_date return date deterministic is
v_date date;
begin
dbms_stats.CONVERT_RAW_VALUE('64640000010101', v_date);
return v_date;
end;
/
CREATE OR REPLACE FUNCTION FUN_DISPLAY_RAW_LHR(P_RAWVAL RAW,
P_TYPE VARCHAR2)
RETURN VARCHAR2 IS
V_NUMBER NUMBER;
V_VARCHAR2 VARCHAR2(32);
V_DATE DATE;
V_NVARCHAR2 NVARCHAR2(32);
V_ROWID ROWID;
V_CHAR CHAR(32);
BEGIN
IF (P_TYPE = 'NUMBER' OR P_TYPE = 'FLOAT') THEN
DBMS_STATS.CONVERT_RAW_VALUE(P_RAWVAL, V_NUMBER);
RETURN TO_CHAR(V_NUMBER);
ELSIF (P_TYPE = 'VARCHAR2') THEN
DBMS_STATS.CONVERT_RAW_VALUE(P_RAWVAL, V_VARCHAR2);
RETURN TO_CHAR(V_VARCHAR2);
ELSIF (P_TYPE = 'DATE' OR P_TYPE LIKE 'TIMESTAMP%') THEN
DBMS_STATS.CONVERT_RAW_VALUE(P_RAWVAL, V_DATE);
RETURN TO_CHAR(V_DATE);
ELSIF (P_TYPE = 'NVARCHAR2') THEN
DBMS_STATS.CONVERT_RAW_VALUE(P_RAWVAL, V_NVARCHAR2);
RETURN TO_CHAR(V_NVARCHAR2);
ELSIF (P_TYPE = 'ROWID') THEN
DBMS_STATS.CONVERT_RAW_VALUE(P_RAWVAL, V_ROWID);
RETURN TO_CHAR(V_ROWID);
ELSIF (P_TYPE = 'CHAR') THEN
DBMS_STATS.CONVERT_RAW_VALUE(P_RAWVAL, V_CHAR);
RETURN TO_CHAR(V_CHAR);
ELSIF (P_TYPE = 'RAW') THEN
RETURN TO_CHAR(P_RAWVAL);
ELSE
RETURN 'UNKNOWN DATATYPE!';
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN 'ERRORS!';
END FUN_DISPLAY_RAW_LHR;
/
SQL> select FUN_DISPLAY_RAW_LHR('c2394b','NUMBER') LOW_VALUE1 from dual;
LOW_VALUE1
--------------------------------------------------------------------------------
5674
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/69924215/viewspace-3050837/ ，如需转载，请注明出处，否则将追究法律责任。
