v$parameter 视图可以查看参数是否可以动态更改(没有隐含参数)
通过v$fixed_view_defination 查看v$parameter 的来源
v$parameter 来自于GV$PARAMETER 所以直接查看
select view_definition from v_$fixed_view_definition where view_name='GV$PARAMETER'
gv$parameter 来源为:
select x.inst_id,
x.indx + 1,
ksppinm,
ksppity,
ksppstvl,
ksppstdvl,
ksppstdf,
decode(bitand(ksppiflg / 256, 1), 1, 'TRUE', 'FALSE'),
decode(b itand(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)
and ((translate(ksppinm, '_', '#') n ot like '##%') and
((translate(ksppinm, '_', '#') not like '#%') or
(ksppstdf = 'FALSE') or (bitand(ksppstvf, 5) > 0)))
将“_”去掉后,v$parameter 就可以查看隐含参数了,标红的两个参数表示是session可以更改和系统更改的情况
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
and
x.ksppinm like'%_gc_affinity%';
查看参数更改生效:
select
x.ksppinm ,
decode(bitand(ksppiflg / 256, 1), 1, 'TRUE', 'FALSE')
ISSES_MODIFIABLE ,
decode(bitand(ksppiflg / 65536, 3) ,
1,
'IMMEDIATE',
2,
'DEFERRED',
3,
'IMMEDIATE',
'FALSE')
ISSYS_MODIFIABLE
from x$ksppi x, x$ksppcv y
where x.indx = y.indx
and
x.ksppinm like'%_gc_affinity%';
KSPPINM ISSES_MODIFIABLE ISSYS_MODIFIABLE
-------------------------------------------------------------------------------- ---------------- ----------------
_gc_affinity_time FALSE FALSE
_gc_affinity_limit FALSE IMMEDIATE
_gc_affinity_minimum FALSE IMMEDIATE
最后2个参数应该是更改后就生效.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8135069/viewspace-664837/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/8135069/viewspace-664837/