From Oracle9i after the launch of the default initialization file is SPFile, we can determine whether it is spfile or pfile way to start the database in the following three ways.
1. Show Parameter SPFile
2. Show Parameter Pfile
3. See V$spparameter View
1, by viewing spfile, Pfile view
Start the database with SPFile:
Sql> Show parameter pfile;
NAME TYPE VALUE
------------------------------------ -------- ------------------------------
SPFile string C:\ORACLE\PRODUCT\10.2.0\DB_1\
DATABASE\SPFILEWWL. ORA
Sql> Show parameter SPFile;
NAME TYPE VALUE
------------------------------------ -------- ------------------------------
SPFile string C:\ORACLE\PRODUCT\10.2.0\DB_1\
DATABASE\SPFILEWWL. ORA
Starting the database with Pfile
Sql> Startup Pfile=c:\initwwl.ora
ORACLE instance started.
Total System Global area 1610612736 bytes
Fixed Size 2066080 bytes
Variable Size 385878368 bytes
Database buffers 1207959552 bytes
Redo buffers 14708736 bytes
Database mounted.
Database opened.
sql> Col Type format A8
Sql> Show parameter pfile;
NAME TYPE VALUE
------------------------------------ -------- ------------------------------
SPFile string
Sql> Show parameter SPFile;
NAME TYPE VALUE
------------------------------------ -------- ------------------------------
SPFile string
Sql>
We can clearly find here that using SPFile to start the database, you can see the path to the SPFile parameter file in view of show parameter Pfile and show parameter SPFile.
Instead of using the Pfile startup database, we cannot see the path to the SPFile parameter file either by viewing show parameter pfile or show parameter pfile.
2. Through the V$spparameter view
Using SPFile to start the database, we can see that the result of the query is SPFile
Sql> Select Decode (COUNT (*), 1, ' spfile ', ' pfile ') from V$spparameter where rownum=1 and isspecified = ' TRUE ';
DECODE (COUNT
------------
SPFile
Using Pfile to start the database, we can see that the result of the query is pfile.
Sql> Select Decode (COUNT (*), 1, ' spfile ', ' pfile ') from V$spparameter where rownum=1 and isspecified = ' TRUE ';
DECODE (COUNT
------------
Pfile
Determine if Oracle is using SPFile or pfile at startup