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.