Initialization Parameters: SPFILE and PFILE

Oracle DBA Certification 12 min min read Updated: Nov 16, 2026 Beginner
Initialization Parameters: SPFILE and PFILE
Beginner Topic 4 of 6

What are Parameters?

Parameters are the settings of an instance: memory sizes, number of processes, file locations and many more. They are read when the instance starts.

SPFILE vs PFILE

FileFormatCan Oracle change it?
SPFILEBinary file (spfile.ora)Yes, with ALTER SYSTEM. Preferred.
PFILEPlain text (init.ora)No, you edit it by hand
Which one is in use? Run SHOW PARAMETER spfile. If the value is empty, the instance started from a PFILE.

Look at Parameters

sql SHOW PARAMETER processes SHOW PARAMETER db_name SELECT name, value, isdefault, issys_modifiable FROM v$parameter WHERE name IN ('processes','open_cursors','sga_target');

Change a Parameter

sql ALTER SYSTEM SET open_cursors = 500 SCOPE = BOTH; ALTER SYSTEM SET processes = 400 SCOPE = SPFILE;
SCOPEEffect
MEMORYChanges the running instance only. Lost at restart.
SPFILESaved in the SPFILE. Takes effect at the next restart.
BOTHDoes both. Default when an SPFILE is used.
Static vs dynamic: Dynamic parameters (like open_cursors) can change while the database runs. Static parameters (like processes) can only use SCOPE=SPFILE and need a restart. The column issys_modifiable tells you which is which: IMMEDIATE, DEFERRED or FALSE.

Reset to Default

sql ALTER SYSTEM RESET open_cursors SCOPE = SPFILE;

Back Up the Parameters

sql CREATE PFILE = '/tmp/init_backup.ora' FROM SPFILE; -- restore later CREATE SPFILE FROM PFILE = '/tmp/init_backup.ora';
Good habit: Before changing parameters, run CREATE PFILE ... FROM SPFILE. If the instance fails to start after a wrong change, start with that PFILE and recreate the SPFILE.

PDB-Level Parameters

In a multitenant database, some parameters can be set inside a PDB. Check the column ispdb_modifiable:

sql ALTER SESSION SET CONTAINER = FREEPDB1; ALTER SYSTEM SET open_cursors = 300 SCOPE = BOTH;

Important Parameters to Know

ParameterMeaning
db_nameName of the database
processes, sessionsLimits on connections
sga_target, pga_aggregate_targetMemory sizing
undo_tablespaceWhich undo tablespace is active
db_recovery_file_destFast Recovery Area location
control_filesControl file locations

Conclusion

Use an SPFILE, change parameters with ALTER SYSTEM and always choose the SCOPE on purpose. Keep a text backup before big changes.

Quick Summary: SPFILE is binary and managed by Oracle. SCOPE=BOTH changes now and later. Static parameters need a restart.

Get Newsletter

Subscibe to our newsletter and we will notify you about the newest updates on Edugators