Show hidden parameters in Oracle

Check the hidden underscore parameters

col "Parameter" for a50
col "Session Value" for a15
col "Instance Value" for a15

select a.ksppinm "Parameter",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
from x$ksppi a, x$ksppcv b, x$ksppsv c
where a.indx = b.indx and a.indx = c.indx
and substr(ksppinm,1,1)='_'
and lower(a.ksppinm) like lower('%&param%')
order by a.ksppinm;

Setting the parameter:

alter system set "&param." = value scope = &scope. ;

scope is one of the following: “both”, “memory”, “spfile”

To remove completely the hidden parameter from the spfile:

alter system reset "&param." scope=spfile SID='*';

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.