前天看了小荷的一个案例因为丢了表上的数据，从索引block中找回了值
转储了oracle block的值，如何得到它真正表中的值，也算 是dump(val,16)的逆运算
sys@ORCL>conn anbob/anbob Connected. anbob@ORCL>create table testdump(id number,name varchar2(10),ctime date); Table created. anbob@ORCL>insert into testdump values(918,'anbob',sysdate); 1 row created. anbob@ORCL>select * from testdump; ID NAME CTIME ---------- ---------- ------------------- 918 anbob 2012-05-04 09:39:23 anbob@ORCL>select dbms_rowid.ROWID_RELATIVE_FNO(rowid) fno,dbms_rowid.rowid_block_number(rowid) blkno 2 from testdump; FNO BLKNO ---------- ---------- 15 2676436 sys@ORCL>alter system dump datafile 15 block 2676436; System altered.
trace file 的部分内容
Start dump data blocks tsn: 13 file#: 15 minblk 2676436 maxblk 2676436 buffer tsn: 13 rdba: 0x03e8d6d4 (15/2676436) Block header dump: 0x03e8d6d4 Object id on Block? Y seg/obj: 0xf58a9 csc: 0x03.74dc2b6 itc: 2 flg: E typ: 1 - DATA brn: 0 bdba: 0x3e8d6d1 ver: 0x01 opc: 0 inc: 0 exflg: 0 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0006.013.0000eb55 0x00800060.58d9.08 --U- 1 fsc 0x0000.074dc31d 0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 data_block_dump,data header at 0xbe72464 =============== tsiz: 0x1f98 hsiz: 0x14 pbl: 0x0be72464 bdba: 0x03e8d6d4 76543210 flag=-------- ntab=1 nrow=1 frre=-1 fsbo=0x14 fseo=0x1f83 avsp=0x1f6f tosp=0x1f6f 0xe:pti[0] nrow=1 offs=0 0x12:pri[0] offs=0x1f83 block_row_dump: tab 0, row 0, @0x1f83 tl: 21 fb: --H-FL-- lb: 0x1 cc: 3 col 0: [ 3] c2 0a 13 col 1: [ 5] 61 6e 62 6f 62 col 2: [ 7] 78 70 05 04 0a 28 18 end_of_block_dump End dump data blocks tsn: 13 file#: 15 minblk 2676436 maxblk 2676436
–分析ump
根据seg/obj: 0xf58a9我们先确认是这个对象
sys@ORCL>select object_id from dba_objects where owner='ANBOB' and object_name='TESTDUMP';
OBJECT_ID
----------
1005737
sys@ORCL>select to_number('f58a9','xxxxxxx') from dual;
TO_NUMBER('F58A9','XXXXXXX')
----------------------------
1005737
sys@ORCL>select objd,ts#,status from v$bh where file#=15 and block#=2676436;
OBJD TS# STATUS
---------- ---------- -------
1005737 13 xcur
根据bdba: 0x03e8d6d4 也可以确认到那个块
anbob@ORCL>variable dba varchar2(30) anbob@ORCL>exec :dba := dbms_utility.make_data_block_address(15,2676436); PL/SQL procedure successfully completed. anbob@ORCL>print dba DBA -------------------------------- 65590996 anbob@ORCL>select to_char(65590996,'xxxxxxxxxxx') from dual; TO_CHAR(6559 ------------ 3e8d6d4
现在我们向回还原表中的值
col 0: [ 3] c2 0a 13 col 1: [ 5] 61 6e 62 6f 62 col 2: [ 7] 78 70 05 04 0a 28 18
第一列为number，看到老白有这种推算方法
0a 转换成10进制-1，13同理，然后再合并，我们试一下
anbob@ORCL>select to_number('0a','xxxxxxx')-1||to_number('13','xxxxx')-1 from dual;
TO_NUMBER('0A','XXXXXXX')-1||TO_NUMBER('13','XXXXX')-1
------------------------------------------------------
918
我们再试下接着推算负数
anbob@ORCL>select dump(-98,16) from dual;
DUMP(-98,16)
--------------------
Typ=2 Len=3: 3e,3,66
anbob@ORCL>select dump(-98571,16) from dual;
DUMP(-98571,16)
---------------------------
Typ=2 Len=5: 3c,5c,10,1e,66
anbob@ORCL>select 101-to_number('3','xxxxxxx') from dual;
101-TO_NUMBER('3','XXXXXXX')
----------------------------
98
anbob@ORCL>select 101-to_number('5c','xxxxxxx'),101-to_number('10','xxxxxxx'),101-to_number('1e','xxxxxxx') from dual;
101-TO_NUMBER('5C','XXXXXXX') 101-TO_NUMBER('10','XXXXXXX') 101-TO_NUMBER('1E','XXXXXXX')
----------------------------- ----------------------------- -----------------------------
9 85 71
往回走是去最后的66，和第一组3e中间用101-（16输为10进制）再组合再一起
有点意思，放开oracle的内部原理不说，其实我们可以用一个ORACLE的函数很方便就可以算出
anbob@ORCL>SELECT utl_raw.cast_to_number(replace('3c,5c,10,1e,66',',')) value FROM dual;
VALUE
----------
-98571
anbob@ORCL>SELECT utl_raw.cast_to_number(replace('c2 0a 13',' ')) value FROM dual;
VALUE
----------
918
第二列为字符col 1: [ 5] 61 6e 62 6f 62
对于字符就是把每位ascII码转换为16进制
anbob@ORCL>select chr(to_number('61','xxxxxx')) from dual;
CH
--
a
anbob@ORCL>select chr(to_number('6e','xxxxxx')) from dual;
CH
--
n
anbob@ORCL>select chr(to_number('62','xxxxxx')) from dual;
CH
--
b
anbob@ORCL>select utl_raw.CAST_TO_VARCHAR2('616e626f62') from dual;
anbob@ORCL>select utl_raw.CAST_TO_VARCHAR2('616e626f62') from dual;
UTL_RAW.CAST_TO_VARCHAR2('616E626F62')
----------------------------------------------------
anbob
第三列是date类型col 2: [ 7] 78 70 05 04 0a 28 18 实值为2012-05-04 09:39:23
anbob@ORCL>select to_number(val16,'xxxxx') val10 from (select regexp_substr('78 70 05 04 0a 28 18','[^ ]+',1,rownum) val16 from dual connect by rownum<=7);
VAL10
----------
120
112
5
4
10
40
24
7 rows selected.
年份-100，月日原值，时分秒-1再组合
note:
dump block 中只会记录col 值和长度，而不会记录col datatype,列类型在数据字典中。