init.ora – parameters

here some issues regarding init.ora parameters

show hidden parameters:

sqlplus / as sysdba

set lines 180 pages 100
col parameter format a40
col session_value format a10
col instance_value format a10
col is_def format a5
col type format a10
col description format a50

select a.ksppinm parameter, b.ksppstvl session_value, c.ksppstvl instance_value, b.ksppstdf is_def,
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, x$ksppsv c
where a.indx = b.indx
  and a.indx = c.indx
  and a.ksppinm like '\_%' escape '\'
order by is_def, parameter asc
/

set a hidden parameter for the current session

 alter session set "_<hiddenparameter>"=<value>;

set a hidden parameter for the whole system

alter system set "_<hiddenparameter>"=<value>;

reset a parameter to the default value

alter system reset "_<hiddenparameter>"=<value> scope=spfile sid='*';

this requires a restart of the instance

an other option to see the current init.ora-settings (hidden and non-hidden parameters) is to take a 10053 optimizer trace.

in the tracefile you find ALL parameters.

how to obtain a 10053-trace click here

backup spfile to pfile

you cannot change a spfile by editing !
so before changing the spfile, it’s sometimes better to have a “normal” file to recreate the spfile.
e.g. if you change a parameter with the spfile-clause and you have afterwards problems at the restart of the instance ..

to create a pfile from spfile:

create pfile ='/ora/pfile_SID_date' from spfile;

also it’s not a bad idea to do this regulary 🙂

Take care of changing hidden-parameters !!! This should normally only done if Oracle support recommends this !!!!!