Introduction
Oracle database parameters are settings that define how the database operates, influencing memory allocation, resource limits, security, and performance. These parameters are stored in a parameter file, either a text file (PFILE) or a binary file (SPFILE). Understanding how to view, modify, and reason about these parameters correctly is a core DBA skill, since getting the scope or persistence wrong can mean a change silently disappears on the next restart, or worse, requires unplanned downtime to apply. This guide covers how parameters work, how to modify them safely, and how to tell which ones can be changed without a restart.
What Parameters Control
Parameters are used to:
- Set limits for the entire database
- Set limits on database resources
- Set user or process limits
- Control the database's behavior and performance
Parameter Files
- PFILE (Parameter File,
init[SID].ora): a plain text file containing initialization parameters. You can edit it directly, but changes only take effect the next time the instance starts from it. - SPFILE (Server Parameter File,
spfile[SID].ora): a binary file that stores initialization parameters. This is the modern default and is whatALTER SYSTEMcommands withSCOPE=SPFILEorSCOPE=BOTHactually write to.
Understanding SCOPE
The SCOPE clause in ALTER SYSTEM statements determines where and when a parameter change takes effect. There are three options:
- MEMORY: changes the value only in the current instance's memory. The change is lost after the next restart, useful for temporary testing.
- SPFILE: changes the value in the server parameter file only. A database restart is required before the change actually takes effect, this is the only option available for static parameters.
- BOTH: updates the value in both memory and the SPFILE at once. The change applies instantly and also persists across restarts, but this only works for parameters that support dynamic modification.
Dynamic vs. Static Parameters
Some parameters are dynamic, meaning they can be changed while the database is running, and take effect immediately or on next connection. Others are static, meaning they require a database restart before the new value is used at all, regardless of which SCOPE you specify.
Modifying Parameters
Dynamic parameters can be modified while the database is running using ALTER SYSTEM or ALTER SESSION. Static parameters can only be changed via SCOPE=SPFILE, and won't take effect until the next restart.
- ALTER SYSTEM: whenever a parameter is modified this way, Oracle records the statement in the alert log, giving you an audit trail of configuration changes over time.
- ALTER SESSION: changes the parameter's value only for the current session. Other sessions on the same instance are unaffected.
How to Identify Whether a Parameter Is Dynamic
Query v$parameter and check the ISSYS_MODIFIABLE column. If the value is IMMEDIATE, the parameter can be modified dynamically without a restart. Any other value means it's static.
SQL> set lines 333 pages 999
SQL> col name for a30
SQL> col value for a10
SQL> col ISSYS_MODIFIABLE for a30
SQL> select NAME, VALUE, ISSYS_MODIFIABLE
from V$PARAMETER where NAME='archive_lag_target';
NAME VALUE ISSYS_MODIFIABLE
------------------------------ ---------- ------------------------------
archive_lag_target 1200 IMMEDIATE
SQL> select NAME, VALUE, ISSYS_MODIFIABLE from V$PARAMETER where NAME='processes';
NAME VALUE ISSYS_MODIFIABLE
------------------------------ ---------- ------------------------------
processes 300 FALSE
As the output shows, archive_lag_target is dynamic (ISSYS_MODIFIABLE = IMMEDIATE), while processes is static (ISSYS_MODIFIABLE = FALSE), which matches what most DBAs already know: changing processes always requires a restart, no matter how you set it.
For reference, here's a fuller list of dynamic and static parameters.
Sample Commands for Changing Parameters
At Session Level
alter session set nls_date_format= 'DD-MON-YYYY HH24:MI:SS';
At System Level: Dynamic Changes
alter system set archive_lag_target=900;
alter system set archive_lag_target=900 scope=memory sid='*';
alter system set archive_lag_target=900 scope=both sid='*';
SID='*' is used in RAC environments, it applies the change across all instances in the cluster rather than just the one you're currently connected to.
At System Level: Static Changes
alter system set processes=1000 scope=spfile;
alter system set processes=1000 scope=spfile sid='*';
Since processes is static, SCOPE=SPFILE is the only valid option here, attempting SCOPE=MEMORY or SCOPE=BOTH on a static parameter will fail, since the running instance can't apply the change without a restart.
Thank you for reading!
I hope this content has been helpful to you. Your feedback and suggestions are always welcome. Feel free to leave a comment or reach out with any queries.
Abhishek Shrivastava
Oracle DBA with hands-on experience managing production Data Guard, RAC, GoldenGate, and APEX environments. I write practical, tested installation and troubleshooting guides based on real deployment work.
Comments
Post a Comment