How to check if your oracle instance was started using a PFILE or an SPFILE?


You can run the below command:
SQL> select count(*) from v$spparameter where value is not null;

If the result of this query is zero, the instance was started using a PFILE.
If the result is a non-zero value, the instance was started using an SPFILE.

When an instance has been started using a server side parameter file (SPFILE), the v$spparameter contains NOT NULL values for several parameters.
When an instance has been started using the ‘classic’ PFILE, this view only contains NULL values.

You can also run the below command:
SELECT DECODE(value, NULL, ‘PFILE’, ‘SPFILE’) “Init File Type” FROM sys.v_$parameter WHERE name = ‘spfile’

Advertisements

Leave a comment

Filed under Oracle

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s