how to identify a static or dynamic parameter in Oracle
      7 Votes

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