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

[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;