Sunday, March 27, 2016

How to determine if database is using which file spfile/pfile

How to determine if database is using which file spfile/pfile

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

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.

No comments:

Post a Comment