Display hidden parameters with the show parameter command of Oracle

Let the show parameter command of Oracle show the hidden parameter Find internal of "show parameter" by session tracingSQL> select * from v $ version; www.2cto.com BANNER implements 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-Producti On 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 types ------------- ------------------------------ define boolean FALSE optimizer_dynamic_sampling integer 2 optimizer_features_enable string 11.2.0.3 op Timizer_index_caching integer 0 random integer 100 optimizer_mode string ALL_ROWS writable boolean TRUE outer boolean FALSE outer boolean true SQL> oradebug tracefile_name/u01/app/oracle/diag/rdbms/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 limit, ROWNUM; Examing definition of view v $ parameterSQL> SELECT view_definition FROM v $ fixed_view_definition WHERE view_name = 'v $ PARAMETER'; VIEW_DEFINITION limit select NUM, NAME, TYPE, VALUE, DISPLAY_VALUE, ISDEFAULT, ISSES_MODIFIABLE, ISSYS_MODIFIABLE, ISINSTAN CE_MODIFIABLE, ISMODIFIED, ISADJUSTED, ISDEPRECATED, ISBASIC, DESCRIPTION, UPDATE_COMMENT, HASH from GV $ PARAMETER where inst_id = USERENV ('instance ') examing definition of view gv $ parameterhidden init parameters start with character '_' SQL> SELECT view_definition FROM v $ fixed_view_definition WHERE view_name = 'gv $ PARAMETER '; VIEW_DEFINITION 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, 'I mmediate', 2, 'referred', 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') o R (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,201 1, 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 $ values (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, 'referred', 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 parametersSQL> show parameter optimizer name type value values ----------- parameter _ db_file_optimizer_read_count integer 8 _ character boolean TRUE _ optimizer_autostats_job boolean TRUE _ Condition boolean TRUE _ Empty boolean TRUE _ empty string ALL _ optimizer_block_size integer 8192 _ optimizer_cache_stats boolean FALSE _ Empty boolean TRUE _ optimizer_cbqt_factor integer 50 name type value =----------- empty _ Empty boolean TRUE _ optional boolean TRUE _ optimizer_complex_pred_selecti1_boolean TRUE _ required boolean FALSE _ optimizer_connect_by_combine_sw boolean TRUE _ optimizer_connect_by_cost_based boolean TRUE _ required string LINEAR