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