[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'參與運算.