how to identify a static or dynamic parameter in Oracle


How to identify any parameter as static or dynamic ?

Answer:   Check for v$parameter we can find one column ie. ISSYS_MODIFIABLE

select name, value, issys_modifiable from v$parameter ;

Values :-

FALSE -It requires changes to carry out to SPFILE ,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.

set lines 200
col name for a35
col value for a25

Select name,VALUE,ISDEFAULT,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name=’processes’;

NAME                                VALUE                     ISDEFAULT ISSES ISSYS_MOD
———————————– ————————- ——— —– ———
processes                           600                       FALSE     FALSE FALSE

From the Above output we can find that whether a parameter can be modified at INSTANCE level.

ISSES_MODIFIABLE

VARCHAR2(5)

TRUE – the parameter can be changed with ALTER SESSION
FALSE – the parameter cannot be changed with ALTER SESSION

ISSYS_MODIFIABLE

VARCHAR2(9)

IMMEDIATE – the parameter can be changed with ALTER SYSTEM
DEFERRED – the parameter cannot be changed until the next session
FALSE – the parameter cannot be changed with ALTER SYSTEM
ISMODIFIED VARCHAR2(10) Indicates whether the parameter has been modified after instance startup:

  • MODIFIED – Parameter has been modified with ALTER SESSION
  • SYSTEM_MOD – Parameter has been modified with ALTER SYSTEM (which causes all the currently logged in sessions’ values to be modified)
  • FALSE – Parameter has not been modified after instance startup
ISINSTANCE_MODIFIABLE VARCHAR2(5) For parameters that can be changed withALTER SYSTEm, indicates whether the value of the parameter can be different for every instance (TRUE) or whether the parameter must have the same value for all Real Application Clusters instances (FALSE). If theISSYS_MODIFIABLE column is FALSE, then this column is always FALSE.

So our processes parameter cannot be modified at instance level. So we need to update only the spfile, which will be reflected in the next instance startup.

for better understanding i would suggest reading

https://docs.oracle.com/cd/B28359_01/server.111/b28320/dynviews_2085.htm

 

Advertisements

One thought on “how to identify a static or dynamic parameter in Oracle

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