Saturday, August 7, 2010

Checking the Hidden Parameters

In Oracle 11g (R1 and R2) a pfile could be created from the parameters in memory using
create pfile='/home/oracle/memory.ora' from memory;
which contains all the hidden parameters and their current values. But this is not possible on 10g.

Found the below shown query on web which works on 10gR2 and 11gR1 and 11gR2.
select a.ksppinm name,
b.ksppstvl value,
b.ksppstdf deflt,
decode(a.ksppity, 1,
'boolean', 2,
'string', 3,
'number', 4,
'file', a.ksppity) type,
a.ksppdesc description
sys.x$ksppi a, sys.x$ksppcv b
where a.indx = b.indx order by name;