Question

oracle如何区分某个参数是静态参数还是动态参数

Answer 1

The ISSYS_MODIFIABLE column in V$PARAMETER tells us whether the parameters are static or dynamic. Static parameters require the instance to be restarted while dynamic parameters can take effect immediately upon being changed.
SQL> select distinct issys_modifiable from v$parameter;

ISSYS_MODIFIABLE
---------------------------
DEFERRED
FALSE
IMMEDIATE

If the ISSYS_MODIFIABLE value is set to FALSE for a parameter, it means that the parameter cannot change its value in the lifetime of the instance; the database needs to be restarted for changes to take effect. A parameter set toIMMEDATE value means that it is dynamic and can be set to change the present active instance as well as future database restarts. A parameter set to DEFERRED is also dynamic, but changes only affect subsequent sessions, currently active sessions will not be affected and retain the old parameter value.