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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2149548/，如需转载，请注明出处，否则将追究法律责任。