让Oracle的 SHOW PARAMETER 命令显示隐藏参数
来源:互联网 发布:linux开发工程师待遇 编辑:程序博客网 时间:2024/06/15 13:59
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
原文出自:http://blog.csdn.net/staricqxyz/article/details/8624549
- 让Oracle的 SHOW PARAMETER 命令显示隐藏参数
- 让Oracle的 SHOW PARAMETER 命令显示隐藏参数
- 让sqlplus 里的show parameter 直接显示oracle隐含参数
- 用show parameter显示Oracle隐含参数
- 用show parameter显示Oracle隐含参数
- oracle如何设置show parameter显示隐含参数
- oracle parameter-隐藏参数
- 在sqlplus里用show parameter 直接显示oracle隐含参数
- 查Oracle的隐藏参数---创建视图v$parameter
- Oracle show parameter 命令的小技巧,下午无意中发现的
- 调整SQLPlus show parameter命令显示结果列宽
- jQuery的隐藏显示hide(),show()方法
- 显示show()隐藏hide()
- 怎么在RAC环境里SHOW PARAMETER时候可以显示HIDDEN参数
- 【parameter】oracle的隐含参数的查看
- hide隐藏和show显示
- angularJs ng-show命令隐藏
- js中标签的显示与隐藏操作hide&show
- java ssh用注解方式实现依赖注入
- typedef 和结构体变量的定义方法
- shell重定向
- Interface Trip Stop - SRS
- 正则表达式(二)
- 让Oracle的 SHOW PARAMETER 命令显示隐藏参数
- Android的ListView源码中有三种onKey事件
- android 关于Toast重复显示解决方法
- Spider之Scrapy安装介绍
- 浅谈HOOK
- 后台调用js的方法
- nice命令
- Bash: about .bashrc, .bash_profile, .profile, /etc/profile, etc/bash.bashrc and others
- IOS学习笔记2—Objective C—类、属性、方法