CODEWITHSUNDEEP
databaseoracle-database
Jay
Jay

Reputation: 1422

How to check whether Oracle instance is started using pfile or spfile?

IS there a view where i can check whether an instance had been started using a pfile or an spfile?

Upvotes: 14

Views: 82328

Answers (3)

Prokhozhii
Prokhozhii

Reputation: 692

select sys_context('USERENV','ORACLE_HOME') from dual;

Upvotes: 0

Anjan Biswas
Anjan Biswas

Reputation: 7912

This shows database was started by spfile

SQL> show parameter spfile;

NAME    TYPE    VALUE
----    ----   ----------------------------------------------------------
spfile  string /root/apps/oracle/10g/dbs/spfile<DB_NAME>.ora

No values returned means that its started by pfile.

Upvotes: 23

Megha Kashinkunti
Megha Kashinkunti

Reputation: 51

SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type" 
   FROM sys.v_$parameter WHERE name = 'spfile';

Upvotes: 4

Related Questions