查看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