DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-02096 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-02096: Specified Initialization Parameter Is Not Modifiable With This Option

ORA-02096 occurs when you attempt to modify an Oracle initialization parameter using an unsupported scope or level — for example, trying to change a static parameter with SCOPE=MEMORY, or attempting an ALTER SESSION on a system-only parameter. Some Oracle parameters are static and require a database restart to take effect, while others can only be changed at the system level. Understanding the modifiable scope of each parameter before making changes is essential to avoiding this error.

Top 3 Causes & SQL Examples

1. Changing a Static Parameter with SCOPE=MEMORY or SCOPE=BOTH

Static parameters cannot be applied to the running instance. They must be written to the SPFILE and applied after a restart.

-- This will raise ORA-02096
ALTER SYSTEM SET DB_BLOCK_SIZE = 16384 SCOPE=MEMORY;
-- ORA-02096: specified initialization parameter is not modifiable with this option

-- Correct approach: write to SPFILE, then restart
ALTER SYSTEM SET DB_BLOCK_SIZE = 16384 SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;

-- Verify the change
SHOW PARAMETER DB_BLOCK_SIZE;
Enter fullscreen mode Exit fullscreen mode

2. Using ALTER SESSION on a System-Only Parameter

Some parameters do not support session-level modification. Attempting ALTER SESSION on such parameters triggers ORA-02096.

-- Check if a parameter is session-modifiable
SELECT name,
       isses_modifiable,
       issys_modifiable
FROM   v$parameter
WHERE  name = 'undo_management';

-- This will fail if ISSES_MODIFIABLE = FALSE
ALTER SESSION SET UNDO_MANAGEMENT = 'AUTO';
-- ORA-02096

-- Correct approach: use ALTER SYSTEM with appropriate SCOPE
ALTER SYSTEM SET UNDO_MANAGEMENT = 'AUTO' SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;
Enter fullscreen mode Exit fullscreen mode

3. Using SCOPE=SPFILE or SCOPE=BOTH When Running on PFILE

If the database is started with a PFILE (init.ora) rather than an SPFILE, using SCOPE=SPFILE or SCOPE=BOTH is not valid and can trigger related errors including ORA-02096.

-- Check if SPFILE is in use
SHOW PARAMETER SPFILE;
-- If VALUE is NULL, the database is running on PFILE

-- Create SPFILE from existing PFILE
CREATE SPFILE FROM PFILE='/u01/app/oracle/product/19c/dbs/initORCL.ora';

-- Restart to activate SPFILE
SHUTDOWN IMMEDIATE;
STARTUP;

-- Now SCOPE=SPFILE and SCOPE=BOTH are available
ALTER SYSTEM SET PROCESSES = 500 SCOPE=BOTH;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Before changing any parameter, always query V$PARAMETER to understand what scope is permitted:

-- Universal pre-change diagnostic query
SELECT name,
       value,
       isses_modifiable,
       issys_modifiable,
       isinstance_modifiable,
       description
FROM   v$parameter
WHERE  name = :parameter_name;

-- ISSYS_MODIFIABLE values:
-- IMMEDIATE  -> ALTER SYSTEM takes effect immediately
-- DEFERRED   -> ALTER SYSTEM takes effect for new sessions
-- FALSE      -> Requires SCOPE=SPFILE + database restart
Enter fullscreen mode Exit fullscreen mode

If you confirm the parameter is static (ISSYS_MODIFIABLE = FALSE), use:

ALTER SYSTEM SET <parameter_name> = <value> SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  1. Standardize a pre-change checklist using V$PARAMETER. Always verify ISSES_MODIFIABLE and ISSYS_MODIFIABLE before executing any parameter change. Build this check into your team's change management process to avoid unplanned outages. 
-- Quick modifiability check
SELECT name,
       CASE issys_modifiable
            WHEN 'IMMEDIATE' THEN 'No restart needed'
            WHEN 'DEFERRED'  THEN 'New sessions only'
            WHEN 'FALSE'     THEN 'Restart required'
       END AS change_impact
FROM   v$parameter
WHERE  name = :param_name;
Enter fullscreen mode Exit fullscreen mode
  1. Always run production databases on SPFILE and back it up regularly. Using SPFILE gives you the full range of SCOPE options and makes parameter management safer and more auditable. Back up the SPFILE before any change: 
-- Backup SPFILE as readable PFILE before changes
CREATE PFILE='/backup/pfile_pre_change.ora' FROM SPFILE;
Enter fullscreen mode Exit fullscreen mode

Related Errors

  • ORA-32001 — Raised when SCOPE=SPFILE is used but no SPFILE is active.
  • ORA-02097 — Parameter value itself is invalid, often confused with ORA-02096.
  • ORA-01034 — Database unavailable; may appear during restarts triggered by static parameter changes.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)