This post focuses on init.ora parameters. It is not really new topic, but rather a personal reference to some practical queries and scripts. If you are the customer, it’s always handy when you can easily access your own queries.

It is quite simple to get some information on init.ora parameters from SQLPlus. Using a tool like TOAD or SQL Developer make it even easier. Unfortunately I work often at the customer without my own tools and scripts. So commandline and SQLPlus is the only “tools” available to work on the database. It is not an issue to dig through the data dictionary to get any kind of information as long as there is 1-2 view involved. But for querying multiple View’s, X$ views etc it is easier to have something on hand.

OK, what’s different with my queries? Not much, they just fit my needs 🙂 Instead of just querying v$parameter I’ll query as well the X$ views to see as well the hidden parameter and simple description for each parameter.

The first query does a select on X$KSPPI, X$KSPPCV, X$KSPPSV and V$PARAMETER to display all init.ora parameter including the hidden parameters. The result can be limited by adding a part of the parameter name or specify % to see all which then would be a little over 2500 parameters. S stands for it is session modifiable, I stands for it is system modifiable and D show if the parameter does still have the default value or not. I’ve added the query as hip.sql (stands somehow for hidden init parameter) to my small script collection which can be downloaded in the script section.

set linesize 235
col Parameter for a50
col Session for a28
col Instance for a55
col S for a1
col I for a1
col D for a1
col Description for a90

select  
  a.ksppinm  "Parameter", 
  decode(p.isses_modifiable,'FALSE',NULL,NULL,NULL,b.ksppstvl) "Session", 
  c.ksppstvl "Instance",
  decode(p.isses_modifiable,'FALSE','F','TRUE','T') "S",
  decode(p.issys_modifiable,'FALSE','F','TRUE','T','IMMEDIATE','I','DEFERRED','D') "I",
  decode(p.isdefault,'FALSE','F','TRUE','T') "D",
  a.ksppdesc "Description"
from x$ksppi a, x$ksppcv b, x$ksppsv c, v$parameter p
where a.indx = b.indx and a.indx = c.indx
  and p.name(+) = a.ksppinm
  and upper(a.ksppinm) like upper('%&1%')
order by a.ksppinm;

The second script does the same as the first one exempt that it limit the result to the list of parameter which are not default. I’ve added the query as hipf.sql (stands somehow for hidden init parameter false) to my small script collection which can be downloaded in the script section.

set linesize 235 pagesize 200
col Parameter for a50
col Session for a28
col Instance for a55
col S for a1
col I for a1
col D for a1
col Description for a90

select * from (select  
  a.ksppinm  "Parameter", 
  decode(p.isses_modifiable,'FALSE',NULL,NULL,NULL,b.ksppstvl) "Session", 
  c.ksppstvl "Instance",
  decode(p.isses_modifiable,'FALSE','F','TRUE','T') "S",
  decode(p.issys_modifiable,'FALSE','F','TRUE','T','IMMEDIATE','I','DEFERRED','D') "I",
  decode(p.isdefault,'FALSE','F','TRUE','T') "D",
  a.ksppdesc "Description"
from x$ksppi a, x$ksppcv b, x$ksppsv c, v$parameter p
where a.indx = b.indx and a.indx = c.indx
  and p.name(+) = a.ksppinm
  and upper(a.ksppinm) like upper('%&1%')
order by a.ksppinm) where d='F';

A few information on hidden init.ora parameter can be found in the Metalink Note How To Query And Change The Oracle Hidden Parameters In Oracle 10g [315631.1]