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

Find internal of "show parameter" by session tracing

SQL> select * from v$version;

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(ksppiflg/256,1),1,'TRUE','FALSE'),  decode(bitand(ksppiflg/65536,3),1,'I
MMEDIATE',2,'DEFERRED', 				 3,'IMMEDIATE','FALSE'),
  decode(bitand(ksppiflg,4),4,'FALSE',					   decod
e(bitand(ksppiflg/65536,3), 0, 'FALSE', 'TRUE')),     decode(bitand(ksppstvf,7),
1,'MODIFIED',4,'SYSTEM_MOD','FALSE'),  decode(bitand(ksppstvf,2),2,'TRUE','FALSE
'),  decode(bitand(ksppilrmflg/64, 1), 1, 'TRUE', 'FALSE'),  decode(bitand(ksppi
lrmflg/268435456, 1), 1, 'TRUE', 'FALSE'),  ksppdesc, ksppstcmnt, ksppihash  fro
m x$ksppi x, x$ksppcv y where (x.indx = y.indx) and  bitand(ksppiflg,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(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
 14           where (x.indx = y.indx)
 15  /

View created.

SQL> grant select on my_v$parameter_with_hidden to system;

Grant succeeded.

SQL> conn system
Enter password: 
Connected.
SQL> create synonym v$parameter for sys.my_v$parameter_with_hidden;

Synonym created.

able to use "show parameter" to show hidden init parameters

SQL> show parameter optimizer

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
_db_file_optimizer_read_count	     integer	 8
_optimizer_adaptive_cursor_sharing   boolean	 TRUE
_optimizer_adjust_for_nulls	     boolean	 TRUE
_optimizer_autostats_job	     boolean	 TRUE
_optimizer_aw_join_push_enabled      boolean	 TRUE
_optimizer_aw_stats_enabled	     boolean	 TRUE
_optimizer_better_inlist_costing     string	 ALL
_optimizer_block_size		     integer	 8192
_optimizer_cache_stats		     boolean	 FALSE
_optimizer_cartesian_enabled	     boolean	 TRUE
_optimizer_cbqt_factor		     integer	 50

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
_optimizer_cbqt_no_size_restriction  boolean	 TRUE
_optimizer_ceil_cost		     boolean	 TRUE
_optimizer_coalesce_subqueries	     boolean	 TRUE
_optimizer_complex_pred_selectivity  boolean	 TRUE
_optimizer_compute_index_stats	     boolean	 TRUE
_optimizer_connect_by_cb_whr_only    boolean	 FALSE
_optimizer_connect_by_combine_sw     boolean	 TRUE
_optimizer_connect_by_cost_based     boolean	 TRUE
_optimizer_connect_by_elim_dups      boolean	 TRUE
_optimizer_correct_sq_selectivity    boolean	 TRUE
_optimizer_cost_based_transformation string	 LINEAR