[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如何算的

總結

以上是生活随笔為你收集整理的[20171227]表的FULL_HASH_VALUE值的计算2的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。