区分一个参数是动态的还是静态

如何区分一个参数是动态的还是静态的

The ISSYS_MODIFIABLE column in V$PARAMETER tells us whether theparameters are static or dynamic. Static parameters require theinstance to be restarted while dynamic parameters can take effectimmediately upon being changed.

SQL> desc v$parameter
 Name                                     Null?   Type
 ------------------------------------------------- ----------------------------
 NUM                                               NUMBER
 NAME                                              VARCHAR2(80)
 TYPE                                              NUMBER
 VALUE                                             VARCHAR2(4000)
 DISPLAY_VALUE                                     VARCHAR2(4000)
 ISDEFAULT                                         VARCHAR2(9)
 ISSES_MODIFIABLE                                  VARCHAR2(5)
 ISSYS_MODIFIABLE                                  VARCHAR2(9)
 ISINSTANCE_MODIFIABLE                             VARCHAR2(5)
 ISMODIFIED                                        VARCHAR2(10)
 ISADJUSTED                                        VARCHAR2(5)
 ISDEPRECATED                                      VARCHAR2(5)
 ISBASIC                                           VARCHAR2(5)
 DESCRIPTION                                       VARCHAR2(255)
 UPDATE_COMMENT                                    VARCHAR2(255)
 HASH                                              NUMBER

 

 

SQL> select distinct issys_modifiable fromv$parameter;
 
ISSYS_MODIFIABLE
---------------------------
DEFERRED  ----->也是动态参数，对于当前session无效，下一个session生效
FALSE     ----->静态参数，需要重启db才能生效
IMMEDIATE  ----->动态参数，立即生效

If the ISSYS_MODIFIABLE value is set to FALSE for a parameter,it means that the parameter cannot change its value in the lifetimeof the instance; the database needs to be restarted for changes totake effect.
A parameter set to IMMEDATE value means that it is dynamic and canbe set to change the present active instance as well as futuredatabase restarts.
A parameter set to DEFERRED is also dynamic, but changes onlyaffect subsequent sessions, currently active sessions will not beaffected and retain the old parameter value

 

eg：查询db_cache_size是否为动态参数
SQL> select ISSYS_MODIFIABLE from v$parameter wherename='db_cache_size';

ISSYS_MOD
---------
IMMEDIATE  -------》表明是动态参数

 

alter system set db_cache_size=xxxx scope=both|spfile;