OracleInstance & database

Working with the spfile

Shows which spfile the instance is using, where parameter files live and the order Oracle searches for them, and what SCOPE = MEMORY, SPFILE and BOTH do. The ALTER SYSTEM lines are commented out.

Tested on 12c, 19c · verified Sep 2026

ora-spfile.sql

1-- Which spfile is in use? (an empty value means the instance started from a pfile)2SHOW PARAMETER spfile3 4-- Parameter file locations5--   pfile:  $ORACLE_HOME/dbs/init$ORACLE_SID.ora6--   spfile: $ORACLE_HOME/dbs/spfile$ORACLE_SID.ora7-- On RAC, use one shared spfile that every instance points to, for example:8--   SPFILE='/shared_mount/dbname/spfiledbname.ora'   (or a location in ASM)9--10-- Search order at startup:11--   $ORACLE_HOME/dbs/spfile<sid>.ora12--   $ORACLE_HOME/dbs/spfile.ora13--   $ORACLE_HOME/dbs/init<sid>.ora14--15-- Changing a parameter (remove the -- from the line you need):16-- MEMORY: affects the database now, but won't survive a restart17--   ALTER SYSTEM SET parameter = value SCOPE = MEMORY;18-- SPFILE: changes the spfile only; takes effect after a restart (required for static parameters)19--   ALTER SYSTEM SET parameter = value SCOPE = SPFILE;20-- BOTH: changes the running instance and the spfile21--   ALTER SYSTEM SET parameter = value SCOPE = BOTH;22--23-- Some parameters can be changed immediately with ALTER SYSTEM;24-- some can only be changed for a single session with ALTER SESSION.

-- Which spfile is in use? (an empty value means the instance started from a pfile)
SHOW PARAMETER spfile

-- Parameter file locations
--   pfile:  $ORACLE_HOME/dbs/init$ORACLE_SID.ora
--   spfile: $ORACLE_HOME/dbs/spfile$ORACLE_SID.ora
-- On RAC, use one shared spfile that every instance points to, for example:
--   SPFILE='/shared_mount/dbname/spfiledbname.ora'   (or a location in ASM)
--
-- Search order at startup:
--   $ORACLE_HOME/dbs/spfile<sid>.ora
--   $ORACLE_HOME/dbs/spfile.ora
--   $ORACLE_HOME/dbs/init<sid>.ora
--
-- Changing a parameter (remove the -- from the line you need):
-- MEMORY: affects the database now, but won't survive a restart
--   ALTER SYSTEM SET parameter = value SCOPE = MEMORY;
-- SPFILE: changes the spfile only; takes effect after a restart (required for static parameters)
--   ALTER SYSTEM SET parameter = value SCOPE = SPFILE;
-- BOTH: changes the running instance and the spfile
--   ALTER SYSTEM SET parameter = value SCOPE = BOTH;
--
-- Some parameters can be changed immediately with ALTER SYSTEM;
-- some can only be changed for a single session with ALTER SESSION.

Save it as ora-spfile.sql and run it with SQL> @ora-spfile.

Open in denrepo Related tool: Init parameter reference

Helps with

ORA-00020: maximum number of processes (%s) exceeded

More Oracle scripts: Instance & database

Instance status on every nodeHost, instance, version, status, whether logins are allowed, startup time and archiver state for every instance. A quick RAC-wide check after a…
Database and instance overviewOne row per instance: host, version, uptime, role, open mode, archive log mode and whether it's a CDB. The first thing to run when you land on an…
Non-default initialization parametersEvery parameter that has been changed from its default. Handy for comparing two databases or documenting a build.
Processes and sessions against their limitsCurrent and peak usage of processes, sessions and transactions since startup. A PEAK % near 100 means the next connection storm will hit ORA-00020.
Installed components and their statusEverything in DBA_REGISTRY. Anything not VALID after a patch or upgrade needs attention before you hand the database back.
Applied patches (datapatch history)Release updates and one-off patches applied through datapatch, newest first. Confirms the SQL side of a patch actually ran.