[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一样,导致出现大量子光标.