How to determine if database is using spfile

If we want to determine whether by database is using an spfile or pfile then we can use the following to determine it->

show parameter spfile;

or

select value from v$parameter where name='spfile';

NAME              TYPE        VALUE
------------------------------------ ----------- ---------------
spfile              string      ?/dbs/spfile@.ora

The above shows that it is using spfile.

If value returned is null then system is using init.ora file otherwise the mentioned spile is being used.

select isspecified, count(*) from v$spparameter group by isspecified;

ISSPEC   COUNT(*)

------ ----------


FALSE         214


TRUE           61

If you get isspecified is 'TRUE' for some parameters, it means it is using spfile. However if you get only 'FALSE' without any 'TRUE' then it uses init.ora file. The above shows that it is using spfile.

To get more information about pfile and spfile, please follow my earlier post->

http://oracleappsdba1.blogspot.com/2008/09/how-to-use-spfile-for-database-startup.html

Technorati Tags: database,pfile,spfile