Viewing Oracle hidden parameters

A sign of old age is I can no longer remember this instantly – how to look at your Oracle parameter settings, partiticularly the hidden ones

select
name,
value,
isdefault
from
gv$parameter
order by name;

Hidden parameters that always start with an underscore can be found in x$ksppi, along with a description:

select
ksppinm,
ksppdesc
from
x$ksppi
where
substr(ksppinm,1,1) = '_';

If we want to query the value of such a hidden parameter, we have to join the table with x$ksppsv and select its ksppstvl attribute:

select
ksppinm,
ksppstvl
from
x$ksppi a,
x$ksppsv b
where
a.indx=b.indx and
substr(ksppinm,1,1) = '_';

Another method, though this does not provide the full list is to dump out the parameters in use in the instance

create pfile='/tmp/mel.ora' from memory;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s