[20171227]表的FULL_HASH_VALUE值的計算2
[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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20171227]表的FULL_HASH_VALUE值的計算
- 20171228db_link的full_hash_value值的計算
- [20171231]oracle full_hash_value如何計算的總結Oracle
- [20180914]oracle 12c 表 full_hash_value如何計算.txtOracle
- 計算機程式的思維邏輯 (2) ：賦值計算機賦值
- （高階）繼承的值與計算的值繼承
- 已計算的關鍵值和限制的關鍵值
- 樹，計算父節點的值
- 36:計算多項式的值
- 2021-2-17：Java HashMap 的中 key 的雜湊值是如何計算的，為何這麼計算？JavaHashMap
- 簡單的計算最值的MapReduce程式
- 平行計算π值
- Tableau的計算欄位、粒度、聚合、比率、表計算
- 數值計算的可靠性（一）
- 數值計算的可靠性（二）
- 數值計算的可靠性（三）
- 【DA】z檢驗p值的計算
- 雲端計算專業技能的價值
- 《計算方法 》 - 第2章 插值法 - 解題套路
- Octave 數值計算
- oracle計算表的記錄數Oracle
- greenplum分佈鍵的hash值計算分析
- 計算2..n的素數
- 數值計算 插值與擬合
- 你所不知道的大資料、雲端計算，以及無法計算的價值大資料
- MySQL：Innodb表 Data free 的計算概要MySql
- 33:計算分數加減表示式的值
- 如何計算MySQL QPS和TPS的值MySql
- 用c++實現淨現值的計算C++
- 數值計算基礎
- 使用 Python 計算 π 值Python
- OpenCV計算機視覺學習（2）——影像算術運算 & 掩膜mask操作（數值計算，影像融合，邊界填充）OpenCV計算機視覺
- 計算表掃描中執行計劃的COST
- 計算2個日期間的所有日期
- 【計算機演算法】 求字首表示式的值計算機演算法
- 陣列操作，計算組元素的極值函式陣列函式
- 計算PI值到一億位的演算法 (轉)演算法
- 對oracle資料表空間的計算Oracle