让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 ProductionPL/SQL Release 11.2.0.3.0 - ProductionCORE11.2.0.3.0ProductionTNS for Linux: Version 11.2.0.3.0 - ProductionNLSRTL Version 11.2.0.3.0 - ProductionSQL> alter session set sql_trace=true;Session altered.SQL> oradebug setmypidStatement processed.SQL> show parameter optimizerNAME     TYPE VALUE------------------------------------ ----------- ------------------------------optimizer_capture_sql_plan_baselines boolean FALSEoptimizer_dynamic_sampling     integer 2optimizer_features_enable     string 11.2.0.3optimizer_index_caching      integer 0optimizer_index_cost_adj     integer 100optimizer_mode     string ALL_ROWSoptimizer_secure_view_merging     boolean TRUEoptimizer_use_invisible_indexes      boolean FALSEoptimizer_use_pending_statistics     boolean FALSEoptimizer_use_sql_plan_baselines     boolean TRUESQL> 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----------------------------------------------------------------selectNUM , NAME , TYPE , VALUE , DISPLAY_VALUE, ISDEFAULT , ISSES_MODIFIABLE , ISSYS_MODIFIABLE , ISINSTANCE_MODIFIABLE, ISMODIFIED , ISADJUSTED , ISDEPRECATED, ISBASIC, DESCRIPTION, UPDATE_COMMENT, HASHfrom 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,'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'),  decode(bitand(ksppilrmflg/268435456, 1), 1, 'TRUE', 'FALSE'),  ksppdesc, ksppstcmnt, ksppihash  from 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 sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Fri Mar 1 09:01:58 2013Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, Automatic Storage Management, OLAP, Data Miningand Real Application Testing optionsSQL> 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 systemEnter 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 optimizerNAME     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 50NAME     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

1楼xiangsir1小时前

好贴，转了