Script to find hidden or underscore parameter values

Need to find a hidden parameter? You can’t use the ‘show parameter’ syntax as Oracle hides them as the name suggests. This script allows you to find them. Script will prompt for a wildcard parameter string name:

set linesize 250

col name for a35
col descripton for a55

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
 from
 sys.x$ksppi a,
 sys.x$ksppcv b
 where
 a.indx = b.indx
 and
 a.ksppinm like '\_%' escape '\'
 and
 a.ksppinm like '%&param%'
order by
 name;

Leave a Reply