SPFile, PFile - What is used by your running Oracle's instance?

From time to time I need to check what initialization parameters my Oracle's instances are using (init.ora or spfile). You changed your instance's parameters but after restart you can see the old ones? Have you had a similar problem? Have you got enough of it? The solution is simple.

Open sql console and enter sql command like below:
    sqlplus / as sysdba
    SELECT DECODE (value, NULL,'PFILE', 'SPFILE') "Init File Type" FROM v$parameter WHERE name = 'spfile';

Init F
In my case init.ora (PFile) is used. You can do it also another way. I will show you second possible way to check that. Now I will ask for Oracle parameters called 'spfile' and 'pfile' (similar to previous example) In sqlplus console enter:

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string

The rule is simple - if 'spfile' parameter have NULL value it means that Oracle uses PFile (init.ora file) - in another case Oracle uses SPFile.



Good ptonis all around. Truly appreciated.