Viewing pending changes that are set in the SPFILE.

Question

Hi,

How do I see what pending changes have been made to an SPFILE, before I bounce the database. I know I can see changes in alert log, but it may have been a few months when the change had been made?.

for example

alter system set sga_max_size=1024M scope=spfile

This doesnt become active until the next bounce.

Thanks in advance.

Answer 1

I don't know of an official view that does this, but it ought to be feasible to read the spfile as an external table and join it to v$parameter.

Answer 2

You'll get some noise in the results from this for various reasons, but you can get close by:

select name, value from v$spparameter where isspecified = 'TRUE'
  minus
select name, value from v$parameter;

Answer 3

In 11g you can do:

CREATE PFILE='dir/init_current.ora' FROM MEMORY;

and

CREATE PFILE='dir/init_spfile.ora' FROM SPFILE;

and then just compare these text files (sort lines in both files first if necessary).