SQL QUERY TO FIND HIDDEN PARAMETERS AND THEIR VALUES ORACLE

Use this query to find hidden parameters and their values set in oracle database

set lines 200 pages 1000
col "Hidden_parameter" for a20
col "It's Value" for a20
select x.ksppinm as Hidden_parameter,
            y.ksppstvl "It's Value"
     from
  x$ksppi x
  inner join x$ksppcv y on (x.indx = y.indx)
and
  x.ksppinm like '%_ash_sample_all%'
order by x.ksppinm;

Hidden value with its description

set lines 200 pages 1000
col "Hidden_parameter" for a20
col "It's Value" for a20
col Desc for a40
select x.ksppinm as Hidden_parameter,
         y.ksppstvl "It's Value",
		   x.ksppdesc "Desc"	 
     from
  x$ksppi x
  inner join x$ksppcv y on (x.indx = y.indx)
and
  x.ksppinm like '%_awr_mmon_deep_purge_interval%'
order by x.ksppinm;

Leave a Reply