[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'.
--//很少遇到不修改了.