[20171227]表的FULL_HASH_VALUE值的計算2

lfree發表於2017-12-27

[20171227]表的FULL_HASH_VALUE值的計算2.txt

--//上午做了測試,得出結論表的FULL_HASH_VALUE計算就是table_name.owner加上"\01\0\0\0".計算md5的值.
--//當然存在一個大小頭對調的問題.

--//下午繼續探究:
1.環境:
SCOTT@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> select owner,name,namespace,type,hash_value,full_hash_value from V$DB_OBJECT_CACHE where owner='SCOTT' and name='EMP';
OWNER  NAME NAMESPACE       TYPE  HASH_VALUE FULL_HASH_VALUE
------ ---- --------------- ----- ---------- --------------------------------
SCOTT  EMP  TABLE/PROCEDURE TABLE 3800164305 684ea11e3eab602b778e1dd1e281e7d1

$ echo -e -n "EMP.SCOTT\01\0\0\0" | md5sum |sed 's/  -//' | xxd -r -p | od -t x4 | sed  -n  -e 's/^0000000 //' -e 's/ //gp'
684ea11e3eab602b778e1dd1e281e7d1

--//可以發現對上.

2.測試type=VIEW的情況:
SCOTT@book> grant dba to a identified by a;
Grant succeeded.

A@book> create table b(c number);
Table created.

A@book> create view c as select * from b;
View created.

A@book> create view c as select * from b;
View created.

A@book> select owner,name,namespace,type,hash_value,full_hash_value from V$DB_OBJECT_CACHE where type='VIEW' and name='C';
OWNER  NAME NAMESPACE       TYPE HASH_VALUE FULL_HASH_VALUE
------ ---- --------------- ---- ---------- --------------------------------
A      C    TABLE/PROCEDURE VIEW 3962634070 3ca340849a793e9ea0c8b73dec30ff56

$ echo -e -n "C.A\01\0\0\0" | md5sum |sed 's/  -//' | xxd -r -p | od -t x4 | sed  -n  -e 's/^0000000 //' -e 's/ //gp'
3ca340849a793e9ea0c8b73dec30ff56
--//也能對上!!

SCOTT@book> select owner,name,namespace,type,hash_value,full_hash_value from V$DB_OBJECT_CACHE where type='VIEW' and rownum=1;
OWNER  NAME                 NAMESPACE       TYPE HASH_VALUE FULL_HASH_VALUE
------ -------------------- --------------- ---- ---------- --------------------------------
SYS    V$SGA_TARGET_ADVICE  TABLE/PROCEDURE VIEW 1394345029 24d6d02aca363181d9715812531c0445

$ echo -e -n 'V$SGA_TARGET_ADVICE.SYS\01\0\0\0' | md5sum |sed 's/  -//' | xxd -r -p | od -t x4 | sed  -n  -e 's/^0000000 //' -e 's/ //gp'
24d6d02aca363181d9715812531c0445
--//注:應該使用單引號,如果含有$,使用雙引號被當作變數.測試就錯誤,差點有卡在這裡!!例子:
$ echo -e "V$SGA_TARGET_ADVICE.SYS\01\0\0\0"
V.SYS

--//再做一個測試:
A@book> select owner,name,namespace,type,hash_value,full_hash_value from V$DB_OBJECT_CACHE where type='VIEW' and name='DBA_OBJECTS';
OWNER  NAME        NAMESPACE       TYPE HASH_VALUE FULL_HASH_VALUE
------ ----------- --------------- ---- ---------- --------------------------------
SYS    DBA_OBJECTS TABLE/PROCEDURE VIEW 2672038839 3fb90a20e2e5cd29e7c6eeea9f4413b7

$ echo -e -n 'DBA_OBJECTS.SYS\01\0\0\0' | md5sum |sed 's/  -//' | xxd -r -p | od -t x4 | sed  -n  -e 's/^0000000 //' -e 's/ //gp'
3fb90a20e2e5cd29e7c6eeea9f4413b7


3.從以上測試可以聯想到的問題就是後面都是補"\01\0\0\0",為什麼呢?
--//很容易聯想到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.

4.看看type=INDEX的情況:

SYS@book> select * from V$DB_OBJECT_CACHE where  TYPE='INDEX' and owner<>'SYS';
no rows selected

--//說明:很奇怪這裡沒有owner<>'SYS'索引.有點點不理解.先放一放.

SYS@book> select owner,name,namespace,type,hash_value,full_hash_value,status from V$DB_OBJECT_CACHE where  TYPE='INDEX' and owner='SYS' and rownum<=2;
OWNER  NAME           NAMESPACE TYPE  HASH_VALUE FULL_HASH_VALUE                  STATUS
------ -------------- --------- ----- ---------- -------------------------------- -------------------
SYS    WRH$_LATCH_PK  INDEX     INDEX 3608281898 7341652c8c9b4d27a90e3e01d712032a UNKOWN
SYS    I_OBJ#_INTCOL# INDEX     INDEX 3598591747 434a51e7d9b780fb00a6906ed67e2703 VALID

$ echo -e -n 'WRH$_LATCH_PK.SYS\04\0\0\0' | md5sum |sed 's/  -//' | xxd -r -p | od -t x4 | sed  -n  -e 's/^0000000 //' -e 's/ //gp'
7341652c8c9b4d27a90e3e01d712032a

$ echo -e -n 'I_OBJ#_INTCOL#.SYS\04\0\0\0' | md5sum |sed 's/  -//' | xxd -r -p | od -t x4 | sed  -n  -e 's/^0000000 //' -e 's/ //gp'
434a51e7d9b780fb00a6906ed67e2703

5.看看type=CLUSTER的情況:
SYS@book> select owner,name,namespace,type,hash_value,full_hash_value,status from V$DB_OBJECT_CACHE where  TYPE='CLUSTER'  and rownum<=2;
OWNER  NAME                 NAMESPACE TYPE    HASH_VALUE FULL_HASH_VALUE                  STATUS
------ -------------------- --------- ------- ---------- -------------------------------- -------------------
SYS    SMON_SCN_TO_TIME_AUX CLUSTER   CLUSTER 2521435996 e8424a63ffea485921f73a0b964a0f5c VALID
SYS    C_TS#                CLUSTER   CLUSTER  756951544 180ad506720fd4d78bbf1e682d1e29f8 VALID

$ echo -e -n 'SMON_SCN_TO_TIME_AUX.SYS\05\0\0\0' | md5sum |sed 's/  -//' | xxd -r -p | od -t x4 | sed  -n  -e 's/^0000000 //' -e 's/ //gp'
e8424a63ffea485921f73a0b964a0f5c

$ echo -e -n 'C_TS#.SYS\05\0\0\0' | md5sum |sed 's/  -//' | xxd -r -p | od -t x4 | sed  -n  -e 's/^0000000 //' -e 's/ //gp'
180ad506720fd4d78bbf1e682d1e29f8

--//說明判斷正確.

5.看看type=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'參與運算.

6.最後看看type=DBLINK:
SCOTT@book> select sysdate from dual@loopback;

SYSDATE
-------------------
2017-12-27 15:49:23

SYS@book> SELECT KGLNAOWN,kglnaobj,kglnahsv FROM x$kglob where  kglhdnsd='DBLINK' and kglobtyd='CURSOR';
KGLNAOWN KGLNAOBJ                       KGLNAHSV
-------- ------------------------------ --------------------------------
S        LOOPBACK                       d87fceb0044fcc85f047f59f77e55d81
         LOOPBACK                       6ded4489db3d13bf72afc20e3afd9dae
         RECO.ORACLE.COM                022bfb39389939832aaa659c3b1dfeba
--//69=0x45

--//實際上我建立loopback是public,不知道為什麼KGLNAOWN一個是S.猜測多次無法猜出.

A@book> CREATE DATABASE LINK A CONNECT TO A IDENTIFIED BY a USING '192.168.100.78/BOOK';

SYS@book> SELECT KGLHDNSP ,kglnaown,dump(kglnaown,16) c20 ,kglnaobj,kglnahsv FROM x$kglob where  kglhdnsd='DBLINK' ;
KGLHDNSP KGLNAOWN C20                  KGLNAOBJ KGLNAHSV
-------- -------- -------------------- -------- --------------------------------
      69 b        Typ=1 Len=1: 62      A        295be635973bc44911d9f76efb5f521b

--//放棄!!研究到這裡.不知道dblink的FULL_HASH_VALUE如何算的

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2149400/,如需轉載,請註明出處,否則將追究法律責任。

相關文章