Difference between a PFILE and SPFILE.

Today I'm going to write about oracle PFILE and SPFILE parameters. Let's have some definitions first:

- PFILE is statics text file that must be updated with a standard text editor like "notepad" or "vi". This file normally resides on the server and DBA's commomly refer to this file as INIT.ora file.

- SPFILE or Server Parameter File is as persistent binary file that can only be modified with the "ALTER SYSTEM SET" command. Editing an SPFILE will corrupit it and you will not be able to start your database.
Well, now how do I know if my database is using a PFILE or SPFILE? Let's try executing below query into database:

SQL> SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "File Type" FROM sys.v_$parameter WHERE name = 'spfile';

File T
------
SPFILE

SQL>

You can also use the v$spparameter view to check if you are using a PFILE or not.

SQL> desc v$spparameter

Name Null? Type
----------------------------------------- -------- ----------------------------
SID VARCHAR2(80)
NAME VARCHAR2(80)
VALUE VARCHAR2(255)
DISPLAY_VALUE VARCHAR2(255)
ISSPECIFIED VARCHAR2(6)
ORDINAL NUMBER
UPDATE_COMMENT VARCHAR2(255)

SQL> select NAME from v$spparameter where name like '%pfile%';

NAME
--------------------------------------------------------------------------------
spfile

SQL>

If the column value is NULL for all parameters, you are using a PFILE. I'll update this post with more tips soon!!!

Regards,
Marcello Travassos.