How to fix ORA-02095 error in Oracle database: Easy Guide

The ORA-02095 error occurs when you try to modify a static initialization parameter using the `ALTER SYSTEM` or `ALTER SESSION` command.

ORA-02095: specified initialization parameter cannot be modified

Step 1: Check If the Parameter Is Dynamic or Static

SELECT name, issys_modifiable FROM v$parameter WHERE name = 'your_parameter_name';

If the value of issys_modifiable is FALSE Then, it is a static parameter and can not be changed without restarting.

If the value is IMMEDIATE or DEFERRED then it is a Dynamic parameter(modifiable in real-time). For Immediate it can be changed without restart and for Deferred change takes effect in the next session.

SAMPLE OUTPUT:

SELECT name, issys_modifiable FROM v$parameter WHERE name = 'open_cursors';

NAME              ISSYS_MODIFIABLE
---------------  ----------------
open_cursors        IMMEDIATE

Step 2: Modify Static Parameter Using SPFILE

If the parameter is static(The value of ISSYS_MODIFIABLE is IMMEDIATE or DEFERRED), follow these steps:

ALTER SYSTEM SET open_cursors = 500 SCOPE=SPFILE;

Restart the database:

SHUTDOWN IMMEDIATE;
STARTUP;

Step 3: Modify the Parameter Using PFILE (If No SPFILE Exists)

Locate the PFILE (typically $ORACLE_HOME/dbs/init<SID>.ora)
Edit the PFILE manually
Restart the database

Step 4: Check the Privileges

Ensure the user has ALTER SYSTEM privileges:

GRANT ALTER SYSTEM TO user_name;

By following these guidelines, you can successfully resolve ORA-02095 errors in your Oracle database.

Before executing these changes, ensure you understand Oracle’s documentation and best practices. Here are some helpful resources: Oracle Official Documentation