How do you find wheather the instance was started with pfile or spfile
Thhere are 3 different ways :-
1) SELECT name, value FROM v$parameter WHERE name = 'spfile'; //This query will return NULL if you are using PFILE
2) SHOW PARAMETER spfile // This query will returns NULL in the value column if you are using pfile and not spfile
3) SELECT COUNT(*) FROM v$spparameter WHERE value IS NOT NULL; // if the count is non-zero then the instance is using a spfile, and if the count is zero then it is using a pfile: By Default oracle will look into the default location depends on the o/s. Like in unix, oracle will check in $oracle_home/dbs directory and on windows it will check in oracle_home/database directory, and the content of pfile is just text based, but spfile content is in binary format, that is understandable by oracle very well.
Also oracle server always check the spfile or pfile with these sequence :-
