[20240821]建立完善kglob.sql脚本.txt

[20240821]建立完善kglob.sql脚本.txt --//以前写的查询v$db_object_cache视图脚本,有一个缺点,输出太长,看上去非常不美观. --//链接如下:https://blog.itpub.net/267265/viewspace-3015895/=>[20240516]关于v$db_object_cache视图.txt --//修改先执行1次不输出利用set term off,然后利用pr.sql脚本转换为竖向输出. --//另外需要显示namespace的数字信息10,16进制显示,加上kglhdnsp,to_char(kglhdnsp,'FMxx')显示. --//另外还有1个问题,就是11g以下版本不使用PDB,在11g下查询会报错.做一些修改完善: $ cat kglob.sql -- Copyright 2023 lfree. All rights reserved. -- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions. -------------------------------------------------------------------------------- -- -- File name:   kglob.sql -- Purpose:     query v$db_object_cache view -- -- Author:      lfree -- -- Usage: --     @ kglob <sql_id> <hash_value> -- for example --     @ kglob 0 123456678 --     @ kglob 7h35uxf5uhmm1 0 -- -------------------------------------------------------------------------------- set term off head off define noprint='noprint' col tpt_version_old  &noprint new_value _tpt_version_old col tpt_version_new  &noprint new_value _tpt_version_new col tpt_noprint      &noprint new_value _tpt_noprint WITH version AS (SELECT TO_NUMBER (SUBSTR (version, 1, 2)) v FROM v$instance) SELECT CASE WHEN v <= 10 THEN '' ELSE '--' END tpt_version_old       ,CASE WHEN v > 10  THEN '' ELSE '--' END tpt_version_new             FROM version; select  /*+ USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1)) */  inst_id                                                                                                                           INST_ID ,KGLNAOWN                                                                                                                          OWNER ,kglnaobj                                                                                                                          NAME ,kglnadlk                                                                                                                          DB_LINK ,kglhdnsd                                                                                                                          NAMESPACE ,kglobtyd                                                                                                                          TYPE ,kglhdnsp                                                                                                                          NAMESPACE_NUM ,to_char(kglhdnsp,'FMxx')                                                                                                          NAMESPACE_HEX ,kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6                                                                    SHARABLE_MEM ,kglhdldc                                                                                                                          LOADS ,kglhdexc                                                                                                                          EXECUTIONS ,kglhdlkc                                                                                                                          LOCKS ,kglobpc0                                                                                                                          PINS ,decode(kglhdkmk,0,'NO','YES')                                                                                                     KEPT ,kglhdclt                                                                                                                          CHILD_LATCH ,kglhdivc                                                                                                                          INVALIDATIONS ,kglnahsh                                                                                                                          HASH_VALUE ,decode(kglhdlmd,0, 'NONE',1, 'NULL', 2, 'SHARED',3, 'EXCLUSIVE','UNKOWN')                                                         LOCK_MODE ,decode(kglhdpmd,0, 'NONE',1, 'NULL', 2, 'SHARED',3, 'EXCLUSIVE','UNKOWN')                                                         PIN_MODE ,decode(kglobsta,1, 'VALID',2,'VALID_AUTH_ERROR',3,'VALID_COMPILE_ERROR',4,'VALID_UNAUTH',5,'INVALID_UNAUTH',6,'INVALID','UNKOWN') STATUS ,substr(to_char(kglnatim,'YYYY-MM-DD/HH24:MI:SS'),1,19)                                                                            TIMESTAMP ,substr(to_char(kglnaptm,'YYYY-MM-DD/HH24:MI:SS'),1,19)                                                                            PREVIOUS_TIMESTAMP ,kgloblct                                                                                                                          LOCKED_TOTAL ,kglobpct                                                                                                                          PINNED_TOTAL ,kglobprop                                                                                                                         PROPERTY ,kglnahsv                                                                                                                          FULL_HASH_VALUE &&_tpt_version_new ,con_id                                                                                                          CON_ID &&_tpt_version_new ,KGLNACON                                                                                                        CON_NAME ,kglhdadr                                                                                                                          ADDR ,kglnaedn                                                                                                                          EDITION ,KGLOBT03                                                                                                                          SQL_ID &&_tpt_version_old,substr(replace(nvl(decode(kglnaown, null, kglnaobj, kglnaobj||'.'||kglnaown||'.'||'\x'||to_char(kglhdnsp,'FMxx')||'\0\0\0'), '(name not found)'),chr(13),'') ,1,60)  end      OBJECT_STR &&_tpt_version_new,substr(replace(nvl(decode(kglnaown, null, kglnaobj, kglnaobj||'.'||kglnaown||'.'||kglnacon||'\x'||to_char(kglhdnsp,'FMxx')||'\0\0\0'), '(name not found)'),chr(13),'') ,1,60) OBJECT_STR from x$kglob where kglnaobj is not null and (KGLOBT03 = lower('&1') or  KGLNAHSH= &2); set term on head on @ pr --//OBJECT_STR 的输出还有一些细节处理起来太麻烦了,我仅仅设置kglnaown is null时,等于kglnaobj.这样对sql_id is not null有效. --//如果这时sql_id is null,实际上kglnaobj||'.'||kglnacon||'\x'||to_char(kglhdnsp,'FMxx')||'\0\0\0'. --//很少遇到不修改了.