让Oracle的SHOW PARAMETER命令显示隐藏参数


让Oracle的SHOW PARAMETER命令显示隐藏参数
 
Find internal of "show parameter" by session tracing
SQL> select * from v$version;    www.zzzyk.com  
  
BANNER  
--------------------------------------------------------------------------------  
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production  
PL/SQL Release 11.2.0.3.0 - Production  
CORE    11.2.0.3.0  Production  
TNS for Linux: Version 11.2.0.3.0 - Production  
NLSRTL Version 11.2.0.3.0 - Production  
  
SQL> alter session set sql_trace=true;  
  
Session altered.  
  
SQL> oradebug setmypid  
Statement processed.  
SQL> show parameter optimizer  
  
NAME                     TYPE    VALUE  
------------------------------------ ----------- ------------------------------  
optimizer_capture_sql_plan_baselines boolean     FALSE  
optimizer_dynamic_sampling       integer     2  
optimizer_features_enable        string  11.2.0.3  
optimizer_index_caching          integer     0  
optimizer_index_cost_adj         integer     100  
optimizer_mode               string  ALL_ROWS  
optimizer_secure_view_merging        boolean     TRUE  
optimizer_use_invisible_indexes      boolean     FALSE  
optimizer_use_pending_statistics     boolean     FALSE  
optimizer_use_sql_plan_baselines     boolean     TRUE  
SQL> oradebug tracefile_name  
/u01/app/oracle/diag/rdbms/zhongwc/zhongwc/trace/zhongwc_ora_32320.trc  
 
Find internal sql statement in trace file.The major statement in trace file looks like following.
It's clear that "show parameter" actually make query on view v$parameter. 
SELECT NAME NAME_COL_PLUS_SHOW_PARAM,  
       DECODE(TYPE,  
              1,  
              'boolean',  
              2,  
              'string',  
              3,  
              'integer',  
              4,  
              'file',  
              5,  
              'number',  
              6,  
              'big integer',  
              'unknown') TYPE,  
       DISPLAY_VALUE VALUE_CL_PLUS_SHOW_PARAM  
  FROM V$PARAMETER  
 WHERE UPPER(NAME) LIKE UPPER('%optimizer%')  
 ORDER BY NAME_COL_PLUS_SHOW_PARAM, ROWNUM;  
 
Examing definition of view v$parameter
SQL> SELECT view_definition FROM v$fixed_view_definition WHERE view_name = 'V$PARAMETER';  
  
VIEW_DEFINITION  
----------------------------------------------------------------------------------------------------  
select  NUM , NAME , TYPE , VALUE , DISPLAY_VALUE, ISDEFAULT , ISSES_MODIFIABLE , ISSYS_MODIFIABLE ,  
 ISINSTANCE_MODIFIABLE, ISMODIFIED , ISADJUSTED , ISDEPRECATED, ISBASIC, DESCRIPTION, UPDATE_COMMENT  
, HASH  from GV$PARAMETER where inst_id = USERENV('Instance')  
 
Examing definition of view gv$parameter
hidden init parameters start with character '_' 
SQL> SELECT view_definition FROM v$fixed_view_definition WHERE view_name = 'GV$PARAMETER';   
  
VIEW_DEFINITION  
--------------------------------------------------------------------------------  
select x.inst_id,x.indx+1,ksppinm,ksppity,ksppstvl, ksppstdvl, ksppstdf,  decode  
(bitand(ksppi易做图/256,1),1,'TRUE','FALSE'),  decode(bitand(ksppi易做图/65536,3),1,'I  
MMEDIATE',2,'DEFERRED',                  3,'IMMEDIATE','FALSE'),  
  decode(bitand(ksppi易做图,4),4,'FALSE',                     decod  
e(bitand(ksppi易做图/65536,3), 0, 'FALSE', 'TRUE')),     decode(bitand(ksppstvf,7),  
1,'MODIFIED',4,'SYSTEM_MOD','FALSE'),  decode(bitand(ksppstvf,2),2,'TRUE','FALSE  
'),  decode(bitand(ksppilrm易做图/64, 1), 1, 'TRUE', 'FALSE'),  decode(bitand(ksppi  
lrm易做图/268435456, 1), 1, 'TRUE', 'FALSE'),  ksppdesc, ksppstcmnt, ksppihash  fro  
m x$ksppi x, x$ksppcv y where (x.indx = y.indx) and  bitand(ksppi易做图,268435456)  
= 0 and  ((translate(ksppinm,'_','#') not like '##%') and    ((translate(ksppinm  
,'_','#') not like '#%')      or (ksppstdf = 'FALSE') or      (bitand(ksppstvf,5  
) > 0)))  
 
Create pseudo-view of v$paramter
[oracle@zhongwc ~]$ sqlplus / as sysdba  
  
SQL*Plus: Release 11.2.0.3.0 Production on Fri Mar 1 09:01:58 2013  
  
Copyright (c) 1982, 2011, Oracle.  All rights reserved.  
  
  
Connected to:  
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production  
With the Partitioning, Automatic Storage Management, OLAP, Data Mining  
and Real Application Testing options  
  
SQL> create or replace view my_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(ksppi易做图/256,1),1,'TRUE','FALSE'),  
                decode(bitand(ksppi易做图/65536,3),1,'IMMEDIATE',2,'