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.
To check which spfile you are using run below command. If you are using spfile you will get a value otherwise you will not see any value.
SQL> show parameter spfile
