Let us see the session about dynamic parameters in oracle database initially we need to know what is static and dynamic parameter in oracle? how to change that? how its works?

Static parameter:

   The Parameter value changes which can be affected on next the database restart i.e SCOPE=SPFILE

Dynamic Parameter:

   - The Parameter value changes which can be affected on active instance as well as future database restarts. i.e SCOPE=MEMORY and SCOPE=BOTH

   - We can repeatedly change those parameter while database is running also SCOPE=SPFILE using this mode changes will be applied on next startup SCOPE=MEMORY using This mode changes will be applied on current session only once we logoff the parameter will restored on default value SCOPE=BOTH using this mode changes will be applied on current session and next startup

 Note: While making any changes in parameters simultaneously create a pfile from spfile

DESC V$parameter



   NAME               TYPE     VALUE
   ISDEFAULT
   ISSES_MODIFIABLE
   ISSYS_MODIFIABLE
   ISMODIFIED
   ISADJUSTED
   DESCRIPTION
   UPDATE_COMMENT

here ISSES_MODIFIABLE  and ISSYS_MODIFIABLE are Dynamic parameters in oracle  

ISSES_MODIFIABLE :

 - To view the isses_modifiable types use this below query

  Select distinct ISSES_MODIFIABLE  from V$parameter;
   TRUE
   FALSE

 - It has two types

    * TRUE  (DYNAMIC) - changes happened  an active instance and next restart

   * FALSE (STATIC) - not changes happened

   select name,value  from V$parameter where ISESS_MODIFIABLE='TRUE';

  - It ll display  listed of parameter on database .

  - We can change the  parameter value  which appears  in TRUE  

ISSYS_MODIFIABLE:

 - To view the issys_modifiable types use this below query

  Select distinct ISSYS_MODIFIABLE from V$parameter;
   IMMEDIATE
   DEFERRED 
   FALSE

 -It has three types

  *IMMEDIATE (DYNAMIC) - changes happens an active instance and next restart

  *DEFERRED (DYNAMIC) - next restart

  *FALSE (STATIC) - not changes

  select name,value from V$parameter where ISSYS_MODIFIABLE='IMMEDIATE';

  select name,value from V$parameter where ISSYS_MODIFIABLE='DEFERRED';

  select name,value from V$parameter where ISSYS_MODIFIABLE='FALSE';

  RESET PARAMETER COMMAND:

   Alter system reset session_max_open_file scope=spfile sid='*';