How to See hidden Parameters and remove them from SPFILE

sometimes when we set hidden parameters, ofcourse after considering the up and down sides of setting that parameter..If you want to see the value of that hidden parameter and you use The “show parameter” command in SQL*Plus Ideally  shows us only the regular parameters and excludes the hidden ones. It is important to know, though, that  only if a hidden parameter was explicitly set in the database, the “show parameter” will show it. It will not show any hidden parameter with a default untouched value.

Following script can be used to return the name of the  all (+hidden parameter also), its current value and its default value:

select par.ksppinm name,
val.ksppstvl value,
val.ksppstdf def_val
from x$ksppi par,
x$ksppcv val
where par.indx=val.indx
order by 1;

Note:  if you want to remove a hidden parameter, remove it. Don’t set it to its default value.

Instead of creating a pfile from spfile, removing  the entry and recreating the spfile OR just changing  the value to the default value you can use the below command to remove hidden parameter

alter system reset hidden_parameter_name scope=spfile sid='*';

Note: The “reset” command works only when writing to the spfile.


Leave a Reply

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

You are commenting using your 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