Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
We can learn the parameter file in which the database is opened by querying the spfile and pfile parameter value.
If both the spfile and the pfile file exist under the $ORACLE_HOME/dbs directory, the database will automatically open with the spfile if no parameter file is specified during startup.
If the instance opened with Spfile;
It will show the Spfile.
1 2 3 4 5 6 7 8 9 10 11 12 | SQL> set lines 1000 SQL> show parameter spfile; NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ spfile string +DATA/orcl/spfileorcl.ora SQL> show parameter pfile; NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ spfile string +DATA/orcl/spfilorcl.ora |
If the instance opened with Pfile;
It doesnt return any value.
1 2 3 4 5 6 7 8 9 10 11 | SQL> set lines 1000 SQL> show parameter spfile; NAME TYPE VALUE ------------------------------------ -------------------------------------------- ------------------------------ spfile string SQL> show parameter pfile; NAME TYPE VALUE ------------------------------------ -------------------------------------------- ------------------------------ spfile string |
