How to check whether an Oracle database was started with spfile or pfile?

There are several ways to check whether the database was started using spfile or pfile

1. Run

   SHOW PARAMETER SPFILE;
   

   If it returns a path to an spfile in Value field, then spfile was used. If the Value field is blank, then pfle was used to strat up the database:
   

   NAME                         TYPE    VALUE
   ---------------------------- -------- ---------------------
   spfile                       string
   

2. Run ALTER SYSTEM statement and see if it causes an error:

   ALTER SYSTEM SET AUDIT_TRAIL='DB_EXTENDED' SCOPE=SPFILE;
   

   If it was pfile that use used, the following error will occur:
   

   ORA-32001: write to SPFILE requested but no SPFILE is in use
   

3. By using V$SPPARAMETER view:
   

   SELECT ISSPECIFIED, COUNT(*) FROM V$SPPARAMETER GROUP BY ISSPECIFIED;
   

   If pfile was used, FASLE will be returned for all setting - meaning they all come from PFILE:
   

   ISSPEC   COUNT(*)
   ------ ----------
   FALSE         348
   

   If result returned looks something like this:
   

   ISSPEC   COUNT(*)
   ------ ----------
   FALSE         327
   TRUE           21
   

   then we know that SPFILE was used because ISSPECIFIED returned TRUE for some settings, specified in SPFILE.