隐藏参数
隐藏参数的特征:带_符号的都是隐藏参数
我们知道从buffer写数据到disk的时机是当buffer达到40%
从redo log buffer写数据到log是要满足1/3原则,commit,1M这些条件
像这样设置40%,1/3原则,commit,1M的内容都是在隐藏参数里面设置的
核心SQL
-
开启&关闭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; -
追踪
[oracle@db01 trace]$ cd /opt/oracle/diag/rdbms/orcl/orcl/trace/
[oracle@db01 trace]$ vim orcl_ora_2281.trc -
查看
V$PARAMETER
怎么定义的
sys@ORCL>select * fromv$fixed_view_definition
where view_name=‘V$PARAMETER’; -
继续查看
GV$parameter
怎么定义的
sys@ORCL>select * fromv$fixed_view_definition
where view_name=‘GV$PARAMETER’; -
重新定义视图
sys@ORCL>Create view myparameter as … -
现在能查到很多之前隐藏的视图了
sys@ORCL>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 synonymv$parameter
for sys.my_v$parameter_with_hidden; -
切换system用户
sys@ORCL>connect system/oracle
create synonymv$parameter
for sys.my_v$parameter_with_hidden; -
查看隐藏参数
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;