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

[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'参与运算.