How to Change Static parameters in SPFILE and PFILE

Change Static parameters in SPFILE:
Changing static parameters requires the SPFILE option for the SCOPE clause with ALTER SYSTEM Statement and changes applies to SPFILE only. The changes cannot be applied to active Instance and the database needs to be restarted to take effect the modified parameters.

You can identify the static parameters using below query
SQL> select name, value, issys_modifiable from v$parameter
where name='processes';
NAME                     VALUE          ISSYS_MODIFIABLE
------------------- -------------- ---------------------------
Processes               150                 FALSE

All parameter that have the column ISSYS_MODIFIABLE value FALSE in the V$PARAMETER view are STATIC parameters and remaining are DYNAMIC parameters in Oracle database.

The ALTER SYSTEM command with SCOPE=SPFILE the will not update the value in the V$PARAMETER view but it will show the new value in the V$SPPARAMETER view as Oracle SPFILE parameter is updated.

For ex:-
SQL> alter system set processes=200 scope=both;
alter system set processes=200 scope=both
               *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

Above statement failed because “processes” is Static parameter and can be used with SCOPE=SPFILE only.

SQL> alter system set processes=200 scope=spfile;
System altered.

You can view the new value in V$SPPARAMETER view as the SPFILE is updated.

SQL> select name, value, isspecified from v$spparameter
where name ='processes';
NAME                     VALUE         ISSYS_MODIFIABLE
------------------- -------------- ---------------------------
Processes               150                 TRUE

Changing Static parameters in PFILE:
Edit the “$ORACLE_HOME/dbs/init.ora” file with new value and bounce the database to take effect the modified parameters.

Identify weather using PFILE or SPFILE:
To check SPFILE or PFILE used by Database or instance, run below commands.

You can see the Oracle SPFILE location.

SQL > show parameter spfile;
NAME                    TYPE        VALUE
------------------ ----------- -----------------------------------------
spfile                     string          /oracle/v10201/dbs/spfileOradb.ora

SQL > select name, value from v$parameter where name = 'spfile';
NAME          VALUE
------------ ------------------------------------------------------------
spfile             /oracle/v10201/dbs/spfileOradb.ora

SQL> select count(1) from v$spparameter where isspecified = 'TRUE';

Returns a NON-ZERO value if SPFILE is in use.

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/