You can control the behavior of an oracle database by setting Initialization Parameters. Use the ALTER SYSTEM statement to set or change parameters. Some parameters can be changed online using SCOPE=MEMORY or SCOPE=BOTH.

Some other parameters need a restart of the database to be active and can be set in SPFILE only (SCOPE=SPFILE). To find out what parameters can be changed online you can either check the documentation, check the dictionary view V$PARAMETER (column ISSYS_MODIFIABLE) or just try to change them. If you get the following error message:

ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

you try to change a parameter that can only be set in SPFILE.

You need to restart the database to activate the new parameter value. Today i need to know if there are parameters set in SPFILEand still not active because the database was not restarted afterwards. You can do this by querying the view V$SPPARAMETER. The following SELECT can help you to find differences between SPFILE settings and the up-and-running database:

select name, 'IS SPFILE: '||value as value_spfile, 'IS MEMORY: '|| (select value from v$parameter where name = param_diff.name) value_memory from (
select name, value from v$spparameter where isspecified = 'TRUE' and name not in ('control_files', 'nls_language','nls_territory')
minus
select name, value from v$parameter where name not in ('control_files', 'nls_language','nls_territory')) param_diff;

Deutsche Version: Ermittlung von nicht aktiven SPFILE Parametern