[20250509]建立完善hidez.sql脚本.txt

--//我使用自己写的hidez.sql查看oracle隐含参数，发现在pdb下存在一个问题，没有考虑pdb的情况。
--//通过例子说明：

1.环境：
SYS@book> @ ver2
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 21.0.0.0.0
BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

SYS@book> show pdbs
CON_ID CON_NAME OPEN MODE  RESTRICTED
------ -------- ---------- ----------
     2 PDB$SEED READ ONLY  NO
     3 BOOK01P  READ WRITE NO

2.问题提出：
SYS@book> @ hidez ^shared_pool_size$
 NUM N_HEX NAME             DESCRIPTION                  DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
---- ----- ---------------- ---------------------------- ------------- ------------- ------------ ----- ---------
 294   126 shared_pool_size size in bytes of shared pool FALSE         360710144     360710144    FALSE IMMEDIATE
 294   126 shared_pool_size size in bytes of shared pool FALSE         360710144     0            FALSE IMMEDIATE
 294   126 shared_pool_size size in bytes of shared pool FALSE         360710144     0            FALSE IMMEDIATE

--//你可以发现显示3行。实际上我在cdb下修改参数shared_pool_size=360710144，其他pdb下没有设置。如何看出哪里不同，对比以前
--//11g的情况，可以发现x$ksppi , x$ksppcv , x$ksppsv 都增加了CON_ID字段。修改hidez.sql增加cond_id的显示呢？
--//注：tpt/pd2.sql没有包含 x$ksppsv ，无法显示当前会话与系统会话的情况。

SYS@book> @ hidez ^shared_pool_size$
 NUM N_HEX CON_ID CON_ID CON_ID NAME             DESCRIPTION                  DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
---- ----- ------ ------ ------ ---------------- ---------------------------- ------------- ------------- ------------ ----- ---------
 294   126      0      1      0 shared_pool_size size in bytes of shared pool FALSE         360710144     360710144    FALSE IMMEDIATE
 294   126      0      1      3 shared_pool_size size in bytes of shared pool FALSE         360710144     0            FALSE IMMEDIATE
 294   126      0      1      2 shared_pool_size size in bytes of shared pool FALSE         360710144     0            FALSE IMMEDIATE

SYS@book> alter session set container=book01p;
Session altered.

SYS@book> @ hidez ^shared_pool_size$
 NUM N_HEX CON_ID CON_ID CON_ID NAME             DESCRIPTION                  DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
---- ----- ------ ------ ------ ---------------- ---------------------------- ------------- ------------- ------------ ----- ---------
 294   126      0      3      3 shared_pool_size size in bytes of shared pool FALSE         0             0            FALSE IMMEDIATE

SYS@book> alter session set container=PDB$SEED;
Session altered.

SYS@book> @ hidez ^shared_pool_size$
 NUM N_HEX CON_ID CON_ID CON_ID NAME             DESCRIPTION                  DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
---- ----- ------ ------ ------ ---------------- ---------------------------- ------------- ------------- ------------ ----- ---------
 294   126      0      2      2 shared_pool_size size in bytes of shared pool FALSE         0             0            FALSE IMMEDIATE

--//可以看出一个规律x$ksppi.con_id都是等于0，而x$ksppcv.con_id等于当前登录container。而x$ksppsv.con_id在cdb$root下等于0
--//，其它container下等于x$ksppcv.con_id。这样增加1个x$ksppsv.con_id的显示就可以了。

3.修改脚本如下：
$ cat hidez.sql
col name format a40
col description format a66
col session_value format a22
col default_value format a22
col system_value format a22

define noprint='noprint'
set term off
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 <= 11 THEN '' ELSE '--' END tpt_version_old
      ,CASE WHEN v > 11  THEN '' ELSE '--' END tpt_version_new
  FROM version;
set term on

select
   a.indx + 1 num,
   to_char(a.indx + 1, 'XXXX') n_hex,
-- a.con_id,
-- b.con_id,
&&_tpt_version_new   c.con_id,
   a.ksppinm  name,
   a.ksppdesc DESCRIPTION,
   b.ksppstdf DEFAULT_VALUE,
   b.ksppstvl SESSION_VALUE,
   c.ksppstvl SYSTEM_VALUE,
   DECODE (BITAND (a.ksppiflg / 256, 1), 1, 'TRUE', 'FALSE')  ISSES_MODIFIABLE,
   DECODE
       (
          BITAND (a.ksppiflg / 65536, 3)
         ,1, 'IMMEDIATE'
         ,2, 'DEFERRED'
         ,3, 'IMMEDIATE'
         ,'FALSE'
       ) ISSYS_MODIFIABLE
from x$ksppi a, x$ksppcv b, x$ksppsv c
where a.indx = b.indx
 and a.indx = c.indx
-- and lower(a.ksppinm) like lower('%&1%')
--escape '\'
--and regexp_like (lower(a.ksppinm)||' '||lower(a.ksppdesc) ,lower('&1')
--and regexp_like (lower(a.ksppdesc)||' '||lower(a.ksppinm) ,lower('&1')
and regexp_like (lower(a.ksppinm) ,lower('&1')
)
order by 1;