Oracle initialization parameters

When starting an instance Oracle retrieves initialization parameters either from init.ora file (initINSTNAME.ora), which is ascii file, or from spfile (Server Parameter File), which is binary file. Unless specified otherwise (i.e. issuing STARTUP command without PFILE clause) Oracle uses spfile.

Initialization parameters fall into two groups:
  • Static initialization parameters - can't be changed for the running instance. The change needs to be done in spfile and database instance restarted.
  • Dynamic initialization parameters - can be changed with instance running and the change takes effect immediately.

    • When a parameter that requires restart is changed its value is changed in spfile and becomes effective after the restart.

    Database parameters with value different to the value in spfile

    
select p.name, p.value effective_value, spfile.value spfile_value 
  from v$parameter p, 
       v$spparameter spfile
 where p.name = spfile.name
   and upper(p.value) != upper(spfile.value);


    This query will return all database initialization parameters which have value different to the value in spfile. Database initialization parameters can have following scopes: - database level (setting limit on database level) - user or process level (setting limit on user or process level) - database resource (setting limit of a resource)