Is database using pfile (init.ora), or spfile?

Posted on 11 January 2007 by Andrew Fraser

Very useful SQL, from Tom Kyte:

select decode(count(*), 1, 'spfile', 'pfile' ) from v$spparameter where rownum=1 and isspecified='TRUE' ;

If it it using an spfile, an easy way to see the parameters is to generate an init.ora copy:

SQL> conn / as sysdba

(or connect as sysoper)

SQL> create pfile = '/tmp/init.ora' from spfile;

This entry was posted in Scripts. Bookmark the permalink.

One Response to Is database using pfile (init.ora), or spfile?

Michael Dinh says:

5 February 2007 at 17:34

Here is another way.

Not using spfile

OPS$ORACLE@TIGGER > show parameter spfile;

NAME TYPE VALUE
———————————— ———– ——————————
spfile string
OPS$ORACLE@TIGGER >

Using spfile

SYS@RMANPRD> show parameter spfile

NAME TYPE VALUE
———————————— ———– ——————————
spfile string /oracle/10gr2/db_1/dbs/spfileR
MANPRD.ora
SYS@RMANPRD>

Leave a Reply Cancel reply