oracle隐藏视图定义,【学习笔记】showhiddenparameter创建查看隐藏参数视图

天萃荷净

分享一篇开发DBA常用的show hidden parameter&＃xff0c;创建查看隐藏参数视图的案例步骤

1、创建查看隐藏参数视图(show_hidden_v$parameter)

--conn "/ as sysdba"

create or replace view show_hidden_v$parameter

(INST_ID, NUM , NAME , TYPE , VALUE , DISPLAY_VALUE, ISDEFAULT , ISSES_MODIFIABLE , ISSYS_MODIFIABLE , ISINSTANCE_MODIFIABLE,

ISMODIFIED , ISADJUSTED , ISDEPRECATED, DESCRIPTION, UPDATE_COMMENT, HASH)

as

select x.inst_id,

x.indx &＃43; 1,

ksppinm,

ksppity,

ksppstvl,

ksppstdvl,

ksppstdf,

decode(bitand(ksppiflg / 256, 1), 1, &＃39;TRUE&＃39;, &＃39;FALSE&＃39;),

decode(bitand(ksppiflg / 65536, 3),

1,

&＃39;IMMEDIATE&＃39;,

2,

&＃39;DEFERRED&＃39;,

3,

&＃39;IMMEDIATE&＃39;,

&＃39;FALSE&＃39;),

decode(bitand(ksppiflg, 4),

4,

&＃39;FALSE&＃39;,

decode(bitand(ksppiflg / 65536, 3), 0, &＃39;FALSE&＃39;, &＃39;TRUE&＃39;)),

decode(bitand(ksppstvf, 7), 1, &＃39;MODIFIED&＃39;, 4, &＃39;SYSTEM_MOD&＃39;, &＃39;FALSE&＃39;),

decode(bitand(ksppstvf, 2), 2, &＃39;TRUE&＃39;, &＃39;FALSE&＃39;),

decode(bitand(ksppilrmflg / 64, 1), 1, &＃39;TRUE&＃39;, &＃39;FALSE&＃39;),

ksppdesc,

ksppstcmnt,

ksppihash

from x$ksppi x, x$ksppcv y

where (x.indx &＃61; y.indx);

2、授权用户访问隐藏视图(show_hidden_v$parameter)

--conn "/ as sysdba"

grant select on show_hidden_v$parameter to chf;

3、建立同义词(v$parameter)

--conn chf/xifenfei(sys下面已经有该同义词)

create synonym v$parameter for sys.show_hidden_v$parameter;

4、直接查询隐藏参数

select

x.ksppinm name,

x.ksppdesc description,

y.ksppstvl value,

y.ksppstdf isdefault,

decode(bitand(y.ksppstvf,7),1,&＃39;MODIFIED&＃39;,4,&＃39;SYSTEM_MOD&＃39;,&＃39;FALSE&＃39;) ismod,

decode(bitand(y.ksppstvf,2),2,&＃39;TRUE&＃39;,&＃39;FALSE&＃39;) isadjusted from

sys.x$ksppi x,

sys.x$ksppcv y

where

x.inst_id&＃61;userenv(&＃39;Instance&＃39;) and

y.inst_id&＃61;userenv(&＃39;Instance&＃39;) and

x.indx&＃61;y.indx and

x.ksppinm like &＃39;%_&par%&＃39;

order by

translate(x.ksppinm,&＃39;_&＃39;,&＃39;&＃39;);

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】show hidden parameter 创建查看隐藏参数视图