Initialization Parameters: SPFILE and PFILE
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
| File | Format | Can Oracle change it? |
|---|---|---|
| SPFILE | Binary file (spfile | Yes, with ALTER SYSTEM. Preferred. |
| PFILE | Plain text (init | 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;
| SCOPE | Effect |
|---|---|
MEMORY | Changes the running instance only. Lost at restart. |
SPFILE | Saved in the SPFILE. Takes effect at the next restart. |
BOTH | Does 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
| Parameter | Meaning |
|---|---|
db_name | Name of the database |
processes, sessions | Limits on connections |
sga_target, pga_aggregate_target | Memory sizing |
undo_tablespace | Which undo tablespace is active |
db_recovery_file_dest | Fast Recovery Area location |
control_files | Control 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.

