René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

View hidden ora.init parameters

The following sql statemant lists documented parameters, that can be set in the init.ora file. 
select 
  name, 
  value, 
  isdefault
from 
  v$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) = '_';