How to tell if a parameter can be modified in a session/system?

By querying V$parameter one can find if the parameter can be modified in session and/or system.

If isses_modifiable is true then value can be modified in session, if FALSE then it can’t be modified using ALTER SESSION SET..

If issys_modifiable = DEFERRED, the value can be changed with scope=spfile specified (if spfile is used) and the change will take into effect into subsequent sessions.

If issys_modifiable =FALSE, the value can be changed using ALTER SYSTEM only if spfile is used and the change will take into effect after instance restart.

If issys_modifiable = IMMEDIATE, the value can be changed with scope=[memory|both|spfile]

To find out if the database is using spfile is being used refer to the following link https://oraclespin.wordpress.com/2008/06/22/how-to-tell-if-the-database-was-started-with-spfile-or-pfile/

Eg:

SELECT  isses_modifiable, issys_modifiable FROM V$parameter WHERE name = ‘shared_pool_size’;

ISSES   ISSYS_MOD

—– ———
FALSE  IMMEDIATE

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s