[20180914]oracle 12c 表 full_hash_value如何計算.txt
[20180914]oracle 12c 表 full_hash_value如何計算.txt
--//昨天在12c下看錶full_hash_value與11g的full_hash_value不同,不過12c使用pdb,猜測跟PDB有關.
--//透過測試說明問題.
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
--//以上11g的結果.
$ 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.而在12c下:
SCOTT@test01p> @ ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
SCOTT@test01p> select owner,name,namespace,type,hash_value,full_hash_value,con_id,con_name from V$DB_OBJECT_CACHE where owner='SCOTT' and name='EMP';
OWNER NAME NAMESPACE TYPE HASH_VALUE FULL_HASH_VALUE CON_ID CON_NAME
-------------------- -------------------- -------------------- -------------------- ---------- -------------------------------- ---------- --------------------
SCOTT EMP TABLE/PROCEDURE TABLE 1676251406 5675b61ea54d0cd0370c43ab63e9910e 3 TEST01P
--//對比前面可以發現FULL_HASH_VALUE不一樣,也很容易猜測12c 的FULL_HASH_VALUE計算加入CON_NAME的內容.
D:\tools\rlwrap>D:\tools\linux\usr\local\wbin\echo -e -n "EMP.SCOTT.TEST01P\01\0\0\0" | md5sum |sed "s/ -//" | D:\tools\Vim\vim80\xxd -r -p | od -t x4 | sed -n -e "s/^0000000 //" -e "s/ //gp"
5675b61ea54d0cd0370c43ab63e9910e
--//OK,能對上.
--//注:windows要安裝unxutil包.echo不能使用windows下的echo.必須使用ubxutil包的echo(該命令支援-n -e引數)
--//sed 命令格式要使用雙引號.
--//我個人還使用vim自帶的xxd.
--//可以發現12c計算表的full_hash_value是 計算table_name.owner_name.con_name\01\0\0\0的md5sum值.
3.從以上測試可以聯想到的問題就是後面都是補"\01\0\0\0",為什麼呢?
--//很容易聯想到namespace
SYS@test> 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 SCHEDULER CLASS
1 TABLE/PROCEDURE SCHEDULER JOB
1 TABLE/PROCEDURE SCHEDULER PROGRAM
1 TABLE/PROCEDURE SCHEDULER SCHEDULE
1 TABLE/PROCEDURE SCHEDULER WINDOW
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
3 TRIGGER TRIGGER
4 INDEX INDEX
5 CLUSTER CLUSTER
5 CLUSTER CURSOR
10 QUEUE QUEUE
18 PUB SUB INTERNAL INFORMATION PUB SUB INTERNAL INFORMATION
23 RULESET RULESET
24 RESOURCE MANAGER RESOURCE MANAGER CONSUMER GROUP
24 RESOURCE MANAGER RESOURCE MANAGER PLAN
28 SUBSCRIPTION SUBSCRIPTION
38 RULE EVALUATION CONTEXT RULE EVALUATION CONTEXT
45 MULTI-VERSION OBJECT FOR TABLE CURSOR
45 MULTI-VERSION OBJECT FOR TABLE MULTI-VERSIONED OBJECT
48 MULTI-VERSION OBJECT FOR INDEX CURSOR
48 MULTI-VERSION OBJECT FOR INDEX MULTI-VERSIONED OBJECT
51 SCHEDULER GLOBAL ATTRIBUTE CURSOR
51 SCHEDULER GLOBAL ATTRIBUTE SCHEDULER GLOBAL ATTRIBUTE
52 RESOURCE MANAGER CDB RESOURCE MANAGER CDB PLAN
64 EDITION EDITION
69 DBLINK CURSOR
72 OBJECT ID OBJECT ID
73 SCHEMA CURSOR
73 SCHEMA NONE
74 DBINSTANCE CURSOR
75 SQL AREA STATS CURSOR STATS
79 ACCOUNT_STATUS NONE
82 SQL AREA BUILD CURSOR
88 PDB CURSOR
88 PDB PDB
93 AUDIT POLICY AUDIT POLICY
103 OPTIMIZER FINDING Optimizer Finding
104 OPTIMIZER DIRECTIVE OWNER CURSOR
104 OPTIMIZER DIRECTIVE OWNER Optimizer Directive Owner
113 GTT SESSION PRIVATE STATS CURSOR
125 PDBOPER CURSOR
54 rows selected.
--//比如你就不能建立emp的sequence.
SCOTT@test01p> create sequence emp cache 100;
create sequence emp cache 100
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
4.SQL語句的full_hash_value(sql_id)計算還是和以前一樣計算 sql文字\0(不包括分號) md5sum值.
--//這樣帶來一個問題,就是如果不同pdb下的語句如果一樣,由於文字內容一樣,這樣計算的sql_id一樣,導致出現大量子游標.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2214232/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- [20171231]oracle full_hash_value如何計算的總結Oracle
- [20171227]表的FULL_HASH_VALUE值的計算
- [20171227]表的FULL_HASH_VALUE值的計算2
- oracle計算表的記錄數Oracle
- 對oracle資料表空間的計算Oracle
- 20171228db_link的full_hash_value值的計算
- Oracle 12C新特性-RMAN恢復表Oracle
- Oracle 12C 新特性之 恢復表Oracle
- [20191127]表 full Hash Value的計算.txt
- Oracle如何精確計算row的大小Oracle
- oracle 12c裡如何恢復votediskOracle
- 12C SQL Translation Framework.txtSQLFramework
- python 計算txt文字詞頻率Python
- [20190718]12c rman新特性 表恢復.txt
- [20181018]Oracle Database 12c: Data Redaction.txtOracleDatabase
- [20130803]ORACLE 12C RMAN 功能增強.txtOracle
- [20130721]ORACLE 12C Invisible Columns.txtOracle
- Oracle 12c expdp和impdp匯出匯入表Oracle
- Oracle 12c 新特性 - 臨時表undo(TEMP UNDO)Oracle
- Oracle平行計算Oracle
- Oracle date計算Oracle
- Oracle 12c如何建立scott使用者Oracle
- 如何計算自動管理的UNDO表空間大小
- oracle實驗記錄 (全表掃描COST計算方法)Oracle
- Oracle 12cOracle
- oracle實驗記錄 (oracle單表選擇率與基數計算(1))Oracle
- oracle實驗記錄 (oracle單表選擇率與基數計算(2))Oracle
- oracle實驗記錄 (oracle單表選擇率與基數計算(3))Oracle
- Oracle表中資料匯出成 Txt格式的方案Oracle
- 如何計算保證金
- Tableau的計算欄位、粒度、聚合、比率、表計算
- Oracle 成本計算公式Oracle公式
- oracle cost計算方式Oracle
- 【STAT】Oracle 表統計資訊被鎖,如何建立索引Oracle索引
- [20130727]ORACLE 12C使用expdp匯出view資料.txtOracleView
- Oracle 12c 統一審計(Unified Auditing)OracleNifi
- Oracle 12C 新特性之表分割槽部分索引(Partial Indexes)Oracle索引Index
- oracle 12c Deprecation of Oracle StreamsOracle