IS MY DATABASE USING PFILE OR SPFILE?

 
QUESTIONS

 
REQUEST

 
RELATED

 
SOLUTION

SET LINES 300
SET PAGES 999
COL "FILE_TYPE" FOR A9 
COL "PATH" FOR A50 
SELECT DECODE(VALUE, NULL, 'PFILE', 'SPFILE') "FILE_TYPE",
       DECODE(VALUE, NULL, (SELECT NVL(SUBSTR(FILE_SPEC, 1, INSTR(FILE_SPEC, '\', -1, 2) -1), 
	   SUBSTR(FILE_SPEC, 1, INSTR(FILE_SPEC, '/', -1, 2) -1)) || '/dbs/init' || INSTANCE_NAME || '.ora' 
	   FROM DBA_LIBRARIES, V$INSTANCE 
WHERE  LIBRARY_NAME = 'DBMS_SUMADV_LIB'), VALUE ) "PATH" FROM SYS.V_$PARAMETER 
WHERE  NAME = 'spfile';

-- PFILE 

FILE_TYPE  PATH
--------- --------------------------------------------------
PFILE     /oracle/product/12.2.0.1/dbs/initDBPILOT.ora

Spfile can be easily created by

SQL> CREATE SPFILE FROM PFILE;

File created.

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP

-- SPFILE 

FILE_TYPE PATH
--------- --------------------------------------------------
SPFILE    /oracle/product/12.2.0.1/dbs/spfileDBPILOT.ora