Use “show paramter” in sqlplus display Hidden Init Parameter

有时需要参考一些隐藏参数值了解ORACLE内存的限制,在sqlplus 中使用show parameter 显示隐藏参数

show parameter 在sqlplus 中用sql跟踪其实也是查询V$PARAMETER 视图

从v$fixed_view_definition查询上面的视图定义是过滤掉了_开头的参数((translate(ksppinm,’_’,’#’ )not like ‘#%’)

做为DBA可以找个独立的用户不受此限制，重新定义视图，比如system

conn  / as sysdba

create or replace view u_v$parameter_with_hidden
  (NUM,NAME , TYPE, display_Value , ISDEFAULT  , ISSES_MODIFIABLE ,
   ISSYS_MODIFIABLE ,ISMODIFIED  , ISADJUSTED, DESCRIPTION,
   UPDATE_COMMENT)
         as
         select x.indx+1,ksppinm,ksppity,ksppstvl,ksppstdf,
                decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE'),
                decode(bitand(ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',
                3,'IMMEDIATE','FALSE'),  decode(bitand(ksppstvf,7),
                1,'MODIFIED',4,'SYSTEM_MOD','FALSE'),
                decode(bitand(ksppstvf,2),2,'TRUE','FALSE'),
                ksppdesc, ksppstcmnt
          from x$ksppi x, x$ksppcv y
         where (x.indx = y.indx);

grant select on u_v$parameter_with_hidden to system;

conn sytem/xxxxx;

create synonym v$parameter for sys.u_v$parameter_with_hidden;

system@ANBOB>show parameter timeout

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
_aiowait_timeouts                    integer                100
_asm_droptimeout                     integer                60
_asm_emultimeout                     integer                0
_buffer_busy_wait_timeout            integer                100
_cgs_reconfig_timeout                integer                120
_cgs_send_timeout                    integer                300
_controlfile_enqueue_timeout         integer                900
_dlm_send_timeout                    integer                30000
_flashback_logfile_enqueue_timeout   integer                600
...
resumable_timeout                    integer                0
system@ANBOB>conn / as sysdba
Connected.
sys@ANBOB>show parameter timeout

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
distributed_lock_timeout             integer                60
log_checkpoint_timeout               integer                1800
resumable_timeout