[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进制表示。
--参考链接：http://blog.itpub.net/267265/viewspace-1357292/
-- http://blog.itpub.net/267265/viewspace-1365382/
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 也可以对上。