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

天萃荷净

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

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 + 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'),

ksppdesc,

ksppstcmnt,

ksppihash

from x$ksppi x, x$ksppcv y

where (x.indx = 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,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismod,

decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE') isadjusted from

sys.x$ksppi x,

sys.x$ksppcv y

where

x.inst_id=userenv('Instance') and

y.inst_id=userenv('Instance') and

x.indx=y.indx and

x.ksppinm like '%_&par%'

order by

translate(x.ksppinm,'_','');

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

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