How to find if the database is using pfile or spfile?

We need put this at sql command prompt and check it

SQL > select ‘ SPFILE in use :’||value from v$parameter
where name = ‘spfile’
and value is not null ;

SQL > select ‘ PFILE in use ‘||value from v$parameter
where name = ‘spfile’
and value is null;

If pfile is in user then it will display as below :-

SQL> select ‘ PFILE in use ‘||value from v$parameter
  2  where name = ‘spfile’
  3  and value is null;
‘PFILEINUSE’||VALUE

 PFILE in use


SQL> select ‘ SPFILE in use :’||value from v$parameter
  2  where name = ‘spfile’
  3  and value is not null ;
no rows selected


if spfile is in use then it will be as below :-

SQL> select ‘ SPFILE in use :’||value from v$parameter
  2  where name = ‘spfile’
  3  and value is not null;

‘SPFILEINUSE:’||VALUE
——————————————————————————–
 SPFILE in use :\APP\ADMINISTRATOR\PRODUCT\11.1.0\DB_1\DATABASE\SPFILEPIHIST.O
RA

SQL> select ‘ PFILE in use ‘||value from v$parameter
  2  where name = ‘spfile’
  3  and value is null;

no rows selected