[20160302]關于FULL_HASH_VALUE.txt

[20160302]關于FULL_HASH_VALUE.txt

--昨天想給firefox安裝一個電腦插件，無意中發現Calculate Hash的插件：

--它是基于檔案來計算MD5,SHA1,理論講這些算法是一樣的，也可以用它來計算FULL_HASH_VALUE值。

1.建立一個檔案内容如下：

select * from dept where deptno=10^@

--注意一些問題，因為oracle計算FULL_HASH_VALUE是sql串後面還要加入chr(0),而且在vim下普通的文本檔案，vim都會在最後補上0d0a(windows下)。

--是以首先要:set binary,然後轉換成16進制模式，輸入00，在切換回來儲存。

Filename    Size (bytes)    MD5                                 SHA1

a.txt        35             5dc43114e7b9dbbdd574aa4e31f15036    341d98827a8cb1c04095dfe768efe5577ec6b043

2.看看oracle計算的情況：

SCOTT@book> select * from dept where deptno=10;

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     NEW YORK

--sql_id=4xamnunv51w9j

SCOTT@book> select sql_id,hash_value,sql_text from v$sql where sql_id='4xamnunv51w9j';

SQL_ID        HASH_VALUE SQL_TEXT

------------- ---------- ------------------------------------------------------------

4xamnunv51w9j  911274289 select * from dept where deptno=10

SCOTT@book> select name,full_hash_value from V$DB_OBJECT_CACHE where hash_value=911274289 and rownum=1;

NAME                                     FULL_HASH_VALUE

---------------------------------------- --------------------------------

select * from dept where deptno=10       1431c45dbddbb9e74eaa74d53650f131

--很明顯跟上面計算的不一樣。再仔細觀察可以發現oracle計算的FULL_HASH_VALUE結果，實際上颠倒一下。

--比如後面3650f131 ，颠倒一下31f15036,這樣正好對上。

$ cat md5_with_chr0.sql

-- md5.sql computes md5 hash and rearranges bytes for printing on Linux (Little Endian)

--         compare results with V$DB_OBJECT_CACHE.HASH_VALUE (11g)

-- Luca May 2012

-- Note user need execute on DBMS_OBFUSCATION_TOOLKIT

-- usage @md5 'string'

-- Example: @md5 'select 1 from dual'

-- calculated

column md5hash format a100

select lower(rawtohex(UTL_RAW.CAST_TO_RAW(DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING =>'&1'||chr(0))))) md5hash from dual;

with calc as (

select lower(rawtohex(UTL_RAW.CAST_TO_RAW(DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING =>'&1'||chr(0))))) md5hash from dual

)

select substr(md5hash,7,2)||substr(md5hash,5,2)||substr(md5hash,3,2)||substr(md5hash,1,2)||

       substr(md5hash,15,2)||substr(md5hash,13,2)||substr(md5hash,11,2)||substr(md5hash,9,2)||

       substr(md5hash,23,2)||substr(md5hash,21,2)||substr(md5hash,19,2)||substr(md5hash,17,2)||

       substr(md5hash,31,2)||substr(md5hash,29,2)||substr(md5hash,27,2)||substr(md5hash,25,2) calculated_full_hash

       from calc;

SCOTT@book> @ &r/md5_with_chr0.sql 'select * from dept where deptno=10'

MD5HASH

--------------------------------

5dc43114e7b9dbbdd574aa4e31f15036

CALCULATED_FULL_HASH

1431c45dbddbb9e74eaa74d53650f131

--這樣正好對上了。

--再來重溫一下hash_value,sql_id的計算。

--sql_id的計算是使用MD5算法進行哈希，生成一個128位的Hash Value(也就是FULL_HASH_VALUE 32位)，其中低32位作為HASH VALUE顯示，SQL_ID則取了後64位。

--實際上sql_id使用32進制表示，hash_value使用10進制表示。

SELECT hash_value

      ,TO_CHAR (hash_value, 'xxxxxxxx') hex_hash_value

      ,full_hash_value

      ,SUBSTR (full_hash_value, -8) hash_x

  FROM GV$DB_OBJECT_CACHE

WHERE hash_value = 911274289 AND ROWNUM = 1;

HASH_VALUE HEX_HASH_ FULL_HASH_VALUE                  HASH_X

---------- --------- -------------------------------- ----------------

911274289  3650f131 1431c45dbddbb9e74eaa74d53650f131 3650f131

--對比上面的select sql_id,hash_value,sql_text from v$sql where sql_id='4xamnunv51w9j';hash_value對上。

select to_number(substr(full_hash_value,-16),'xxxxxxxxxxxxxxxxxxxx') sql_id10, full_hash_value

from GV$DB_OBJECT_CACHE  WHERE hash_value = 911274289 AND ROWNUM = 1;

            SQL_ID10 FULL_HASH_VALUE

-------------------- --------------------------------

5668471540087320881 1431c45dbddbb9e74eaa74d53650f131

select replace(wmsys.wm_concat(c),',') from (

select c from (

SELECT SUBSTR ('0123456789abcdfghjkmnpqrstuvwxyz', a + 1, 1) c,rownum rn

  FROM (WITH data (a, b)

             AS (SELECT MOD (&1, 32) a, TRUNC (&1 / 32) b FROM DUAL

                 UNION ALL

                 SELECT MOD (b, 32) a, TRUNC (b / 32) b

                   FROM data

                  WHERE b !=0

                  )

        SELECT a

          FROM data)) order by rn desc);

--帶入5668471540087320881

REPLACE(WMSYS.WM_CONCAT(C),',')

---------------------------------

4xamnunv51w9j

--sql_id 也可以對上。