Use the following query on a started instance (nomount, mount or open):
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.
Explanation:When an instance has been started using a server side parameter file (SPFILE), which is a new feature in Oracle Server 9.0.1, 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.
Oracle DBA FAQ >