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’