让oracle show parameter显示隐藏参数

隐藏参数
隐藏参数的特征：带_符号的都是隐藏参数
我们知道从buffer写数据到disk的时机是当buffer达到40%
从redo log buffer写数据到log是要满足1/3原则，commit，1M这些条件
像这样设置40%，1/3原则，commit，1M的内容都是在隐藏参数里面设置的

核心SQL

1. 开启&关闭trace
   SQL> show parameter sql_trace
   SQL> show user
   SQL> alter session set sql_trace = true;
   SQL> oradebug setmypid
   SQL> oradebug tracefile_name
   SQL> show parameter controlfile
   SQL> alter session set sql_trace = false;

2. 追踪
   [oracle@db01 trace]$ cd /opt/oracle/diag/rdbms/orcl/orcl/trace/
   [oracle@db01 trace]$ vim orcl_ora_2281.trc

3. 查看V$PARAMETER怎么定义的
   sys@ORCL>select * from v$fixed_view_definition where view_name=‘V$PARAMETER’;

4. 继续查看GV$parameter怎么定义的
   sys@ORCL>select * from v$fixed_view_definition where view_name=‘GV$PARAMETER’;

5. 重新定义视图
   sys@ORCL>Create view myparameter as …

6. 现在能查到很多之前隐藏的视图了
   sys@ORCL>select NAME from my_v$parameter_with_hidden where UPPER(NAME) like ‘%CONTROL%’;

7. 授权
   sys@ORCL>grant select on my_v$parameter_with_hidden to public;

8. 创建同义词
   create synonym v$parameter for sys.my_v$parameter_with_hidden;

9. 切换system用户
   sys@ORCL>connect system/oracle
   create synonym v$parameter for sys.my_v$parameter_with_hidden;

10. 查看隐藏参数
    system@ORCL>show parameter control;

实验详细
SQL> show parameter sql_trace

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sql_trace                            boolean     FALSE

SQL> show user

USER is "SYS"

SQL> alter session set sql_trace = true;

Session altered.

SQL> oradebug setmypid

Statement processed.

SQL> oradebug tracefile_name

/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6509.trc

SQL> show parameter controlfile

SQL> alter session set sql_trace = false;

[oracle@db01 trace]$ cd /opt/oracle/diag/rdbms/orcl/orcl/trace/

[oracle@db01 trace]$ vim orcl_ora_2281.trc

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(:NMBIND_SHOW_OBJ) ORDER BY NAME_COL_PLUS_SHOW_PARAM,ROWNUM

由此得知Show parameter 是从 v$parameter里查出来的，
接下来需要看v$parameter是怎么定义的
可以通过v$FIXED_VIEW_DEFINITION查看v$parameter定义

sys@ORCL>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, ISBASIC, DESCRIPTION, UPDATE_COMMENT
, HASH  from GV$PARAMETER where inst_id = USERENV('Instance')

继续往深层里推，由此得知v$parameter来自于GV$parameter
继续查看GV$parameter怎么定义的
sys@ORCL>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'),  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)))

下面这些条件是约束隐藏参数的，只要将这些内容去掉即可显示隐藏参数

and  bitand(ksppiflg,268435456) = 0 
and  ((translate(ksppinm,'_','#') not like '##%') 
and    ((translate(ksppinm,'_','#') not like '#%')      
or (ksppstdf = 'FALSE') 
or      (bitand(ksppstvf,5) > 0)))

创建一个自定义视图

Create view myparameter as 
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);

注意，这里报错了

创建视图时要在as前把（DESC v$parameter）里有的字段加上

create or replace view my_v$parameter_with_hidden  (INST_ID,NUM,NAME , TYPE,VALUE, display_Value , ISDEFAULT  , ISSES_MODIFIABLE ,  ISSYS_MODIFIABLE , ISINSTANCE_MODIFIABLE,ISMODIFIED  , ISADJUSTED, ISDEPRECATED, ISBASIC,DESCRIPTION,  UPDATE_COMMENT, HASH)  
as 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);

现在能查到很多之前隐藏的视图了
select NAME from my_v$parameter_with_hidden where UPPER(NAME) like ‘%CONTROL%’;

授权
sys@ORCL>grant select on my_v$parameter_with_hidden to public;

创建同义词
create synonym v$parameter for sys.my_v$parameter_with_hidden;

切换system用户
sys@ORCL>connect system/oracle
create synonym v$parameter for sys.my_v$parameter_with_hidden;

查看隐藏参数
system@ORCL>show parameter control;