Explore the secrets of Oracle pfile and spfile

Determines whether to use spfile or pfile when Oracle is started.

The default initialization file used after Oracle 9i is spfile. We can use the following three methods to determine whether it is SPFILE or PFILE to start the database.
1. show parameter spfile
2. show parameter pfile
3. view the v $ spparameter view.

 

1. view the spfile and pfile views

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

Start 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>
 
 
Here we can clearly find that the path of the spfile parameter file can be viewed in show parameter pfile and show parameter spfile when using spfile to start the database.
If you use pfile to start a database, you cannot view the path of the pfile parameter file in both show parameter pfile and show parameter spfile.

1 2 3 Next Page