[20171231]oracle full_hash_value如何计算的总结.txt

--//前一段时间测试oracle full_hash_value如何计算的,感觉有必要做一些总结:

1.sql语句的计算参考链接:
--//http://blog.itpub.net/267265/viewspace-2142512/ => [20170724]关于sql_id那些事.txt
--//实际上就是语句结尾加入\0,计算md5,然后4个4个翻转.例子:

$ echo -e -n 'select * from emp where deptno=10\0' | md5sum | sed 's/  -//' | xxd -r -p | od -t x4
0000000 8bb97487 1a4f8c88 529ea488 5efe0842
0000020

2.table等其他对象的full_hash_value计算:

http://blog.itpub.net/267265/viewspace-2149366/
http://blog.itpub.net/267265/viewspace-2149400/

--//实际上就是object_namee.owner\xNN\00\00\00.后面的\xNN\00\00\00与namespace有关.

SYS@book>  select distinct kglhdnsp,kglhdnsd,kglobtyd from x$kglob order by 1;
KGLHDNSP KGLHDNSD                       KGLOBTYD
-------- ------------------------------ ----------------------------------
       0 SQL AREA                       CURSOR
       1 TABLE/PROCEDURE                CURSOR
       1 TABLE/PROCEDURE                FUNCTION
       1 TABLE/PROCEDURE                LIBRARY
       1 TABLE/PROCEDURE                OPERATOR
       1 TABLE/PROCEDURE                PACKAGE
       1 TABLE/PROCEDURE                PROCEDURE
       1 TABLE/PROCEDURE                SEQUENCE
       1 TABLE/PROCEDURE                SYNONYM
       1 TABLE/PROCEDURE                TABLE
       1 TABLE/PROCEDURE                TYPE
       1 TABLE/PROCEDURE                VIEW
       2 BODY                           CURSOR
       2 BODY                           PACKAGE BODY
       2 BODY                           TYPE BODY
       3 TRIGGER                        TRIGGER
       4 INDEX                          INDEX
       5 CLUSTER                        CLUSTER
      10 QUEUE                          QUEUE
      18 PUB SUB INTERNAL INFORMATION   PUB SUB INTERNAL INFORMATION
      23 RULESET                        RULESET
      24 RESOURCE MANAGER               RESOURCE MANAGER CONSUMER GROUP
      45 MULTI-VERSION OBJECT FOR TABLE MULTI-VERSIONED OBJECT
      48 MULTI-VERSION OBJECT FOR INDEX MULTI-VERSIONED OBJECT
      51 SCHEDULER GLOBAL ATTRIBUTE     CURSOR
      51 SCHEDULER GLOBAL ATTRIBUTE     SCHEDULER GLOBAL ATTRIBUTE
      52 SCHEDULER EARLIEST START TIME  SCHEDULER EARLIEST START TIME
      64 EDITION                        EDITION
      69 DBLINK                         CURSOR
      73 SCHEMA                         CURSOR
      73 SCHEMA                         NONE
      74 DBINSTANCE                     CURSOR
      75 SQL AREA STATS                 CURSOR STATS
      79 ACCOUNT_STATUS                 NONE
      82 SQL AREA BUILD                 CURSOR
35 rows selected.

3.DBlink的full_hash_value计算:
--//参考http://blog.itpub.net/267265/viewspace-2149494/
--//有点奇怪的是x$kglob记录的owner(对应x$kglob的KGLNAOWN字段)与实际的不同.

dblink_name.x$kglob.KGLNAOWN\0\0\0\x45\0\0\0.

--//这里的x45 十进制是69.

--//实际上许多对象我没有测,大家可以自行测试.

4.SCHEMA:

SYS@book> SELECT kglnaobj,kglnahsv FROM x$kglob where kglnaobj in ('A','SCOTT') and kglhdnsd='SCHEMA';
KGLNAOBJ KGLNAHSV
-------- --------------------------------
A        e35e107310031d819c9b96a03be48e91
SCOTT    b57d9e745d1d0f49e0530388de8ba781

--//73 =0x49(16进制)

$ echo -e -n 'A\x49\0\0\0' | md5sum |sed 's/  -//' | xxd -r -p | od -t x4 | sed  -n  -e 's/^0000000 //' -e 's/ //gp'
e35e107310031d819c9b96a03be48e91

$ echo -e -n 'SCOTT\x49\0\0\0' | md5sum |sed 's/  -//' | xxd -r -p | od -t x4 | sed  -n  -e 's/^0000000 //' -e 's/ //gp'
b57d9e745d1d0f49e0530388de8ba781

--//schema的计算是owner加上'\x49\0\0\0'参与运算.