Oracle initialization parameters are static & amp; Dynamic

During Oracle learning, we will find that many Oracle knowledge points are in pairs.

So, in order to facilitate understanding and memory, I will make a summary of this series.

The first part of this series is static or dynamic initialization parameters?

In Oracle, spfile is introduced from 9i. Many parameters support online modification, that is, the modification will take effect immediately.

The official documentation provides the following instructions:

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

Check the value of issys_modifiable:

Sys @ ORCL> select distinct issys_modifiable from v $ parameter;

ISSYS_MOD
---------
IMMEDIATE
FALSE
DEFERRED

Parameter value description:

● FALSE: If issys_modifiable is changed to this value, the parameter is static.
● IMMEDIATE: indicates that the parameter is dynamic, can be modified online, and takes effect online.
● DEFERRED: this parameter is dynamic, but the modification is invalid in the current session and takes effect only for the next session.