Search notes:
Oracle: X$KSPPI
x$ksppi is interesting because, unlike
v$parameter, it also shows so called
hidden parameters.
x$ksppi can be joined to x$ksppcv and x$ksppsv:
select
par.ksppinm name,
par.ksppdesc description,
--
v.ksppstvl value_v,
v.ksppstdvl display_value_v,
v.ksppstdfl default_value_v,
--
w.ksppstvl value_w,
w.ksppstdvl display_value_w,
w.ksppstdfl default_value_w,
--
v.ksppstdf is_default_v,
v.ksppstvf,
v.ksppstcmnt update_comment,
--
w.ksppstdf is_default_w,
w.ksppstvf vf_w,
w.ksppstcmnt update_comment_w,
--
par.ksppilrmflg,
par.ksppity,
par.ksppihash
from
x$ksppi par join
x$ksppcv v on par.indx = v.indx join
x$ksppsv w on par.indx = w.indx
where
par.ksppinm like '%db%block%'
order by
lower(replace(par.ksppinm, '_', ''));
TODO
Apparently, x$ksppi can be joined to x$ksppcv2 like so:
select
x.ksppinm name,
ksppdesc description,
y.kspftctxvl value,
y.kspftctxdf isdefault,
decode(bitand(y.kspftctxvf, 7), 1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismod, decode(bitand(y.kspftctxvf, 2), 2, 'TRUE', 'FALSE') isadj
from
sys.x$ksppi x,
sys.x$ksppcv2 y
where
x.inst_id = userenv('instance') and
y.inst_id = userenv('instance') and
x.indx + 1 = y.kspftctxpn
order by
name;