I came across an interesting article written in Chinese on itpub.net. That article shows how to use sql*plus command "Show Parameter" to show hidden init parameters of Oracle.

Please note: Never do it on your production database because it may cause your database corrupt. The original practice was done against Oracle 9ir2, the practice below is done against Oracle 10gr1.

Oracle hidden (undocumented) init parameters are critical for oracle database. And, it should be protected against update unless Oracle support allow you to do that. Therefore, these hidden init parameters are invisible for database DBAs and Developers. To view to value of hidden init parameters, those who are interested in studying Oracle internal usually rely on scripts to display those hidden init parameters.

The question is: Can we check the value of hidden init parameters by using common sql*plus command "show parameters"? The answer is: Absolutely Yes. Now, we'd like to show how to do that step by step.

Step 1: Find internal of "show parameter" by session tracing

**********************************************************************************

SQL> alter session set sql_trace=true; 

SQL> show parameter optimizer 
NAME                                      TYPE               VALUE 
------------------------------------------------------------------------------------------------------ 
optimizer_dynamic_sampling     integer             2 
optimizer_features_enable        string               10.1.0 
optimizer_index_caching           integer             0 
optimizer_index_cost_adj          integer             100 
optimizer_mode                       string               ALL_ROWS 

SQL> exit 

**********************************************************************************

Step 2: Find internal sql statement in trace file

The major statement in trace file looks like following:

**********************************************************************************

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_COL_PLUS_SHOW_PARAM 
FROM V$PARAMETER 
WHERE UPPER(NAME) LIKE UPPER('%optimizer%') 
ORDER BY NAME_COL_PLUS_SHOW_PARAM,ROWNUM

**********************************************************************************

It's clear that "show parameter" actually make query on view v$parameter.

Step 3: Examing definition of view v$parameter

**********************************************************************************

SQL> SELECT * FROM v$fixed_view_definition
WHERE view_name = 'V$PARAMETER'; 

VIEW_NAME          VIEW_DEFINITION 
---------------------------------------------------------------------------------------------------- 
V$PARAMETER      select  NUM , NAME , TYPE , VALUE , DISPLAY_VALUE, 
                                     ISDEFAULT , ISSES_MODIFIABLE , 
                                     ISSYS_MODIFIABLE ,    
                                     ISINSTANCE_MODIFIABLE,                                                 
                                     ISMODIFIED , ISADJUSTED , ISDEPRECATED,                                   
                                     DESCRIPTION,   UPDATE_COMMENT, HASH 
                           from GV$PARAMETER 
                           where inst_id = USERENV('Instance' )

SQL> SELECT * FROM v$fixed_view_definition WHERE view_name = 'GV$PARAMETER'; 

VIEW_NAME            VIEW_DEFINITION 
--------------------------------------------------------------------------------------------------- 
GV$PARAMETER       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, 'IMMEDIATE',2,
                                                     'DEFERRED',3,'IMMEDIATE','FALSE'),    
                                       decode(bitand(ksppiflg,4),4,'FALSE', 
                                       decode(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'),  
                                       ksppdesc, ksppstcmnt, sppihash                                                   
                               from x$ksppi x, x$ksppcv y  
                               where (x.indx = y.indx) and  ((translate(ksppinm,'_','#' )not like '#%') 
                                       or (ksppstdf = 'FALSE'))

**********************************************************************************

The highlighted red part is key to eliminate hidden init parameters while using "show parameter" (hidden init parameters start with character '_' ).

Step 4: Create pseudo-view of v$paramter

**********************************************************************************

SQL> connect sys@orcl as sysdba
Enter password:
Connected.

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
         where (x.indx = y.indx);

View created.

SQL> grant select on my_v$parameter_with_hidden to system;

Grant succeeded.

SQL> connect system@orcl
Enter password:
Connected.

SQL> create synonym v$parameter for sys.my_v$parameter_with_hidden;

Synonym created.

***************************************************************************

Step 5: able to use "show parameter" to show hidden init parameters

***************************************************************************

SQL> show parameter optimizer 

NAME                                                  TYPE        VALUE
-----------------------------------------------------------------------------------------------
_optimizer_adjust_for_nulls                  boolean      TRUE
_optimizer_autostats_job                      boolean     TRUE
_optimizer_block_size                          integer       8192
_optimizer_cache_stats                        boolean      FALSE
_optimizer_cbqt_factor                         integer       50
_optimizer_ceil_cost                             boolean     TRUE
_optimizer_choose_permutation            integer       0
_optimizer_compute_index_stats           boolean     TRUE
_optimizer_correct_sq_selectivity          boolean     TRUE
_optimizer_cost_based_transformation  string         LINEAR
_optimizer_cost_filter_pred                  boolean      FALSE
_optimizer_cost_model                         string        CHOOSE
_optimizer_degree                               integer       0
_optimizer_dim_subq_join_sel               boolean     TRUE
_optimizer_disable_strans_sanity_che    integer      0
cks
_optimizer_dyn_smp_blks                     integer      32
_optimizer_ignore_hints                        boolean     FALSE
_optimizer_invalidation_period              integer       18000
_optimizer_join_order_control               integer       3
_optimizer_join_sel_sanity_check          boolean     TRUE
_optimizer_max_permutations              integer       2000
_optimizer_mjc_enabled                      boolean      TRUE
_optimizer_mode_force                        boolean     TRUE
_optimizer_new_join_card_computation boolean     TRUE
_optimizer_percent_parallel                  integer      101
_optimizer_push_down_distinct             integer       0
_optimizer_random_plan                      integer       0
_optimizer_save_stats                         boolean      TRUE
_optimizer_search_limit                       integer        5
_optimizer_skip_scan_enabled             boolean      TRUE
_optimizer_sortmerge_join_enabled     boolean      TRUE
_optimizer_squ_bottomup                    boolean     TRUE
_optimizer_system_stats_usage           boolean      TRUE
_optimizer_undo_changes                   boolean      FALSE
_optimizer_undo_cost_change             string         10.1.0
optimizer_dynamic_sampling               integer        2
optimizer_features_enable                  string         10.1.0
optimizer_index_caching                     integer        0
optimizer_index_cost_adj                    integer       100
optimizer_mode                                 string          ALL_ROWS

**********************************************************************************

That's it. Again, never do it in production environment!