- 21 Nov, 2021
- 0 Comments
- 1 Min Read
What is difference between static and dynamic parameter in oracle database
What is difference between static and dynamic parameter in oracle database
We need to check v$parameter table and find the coumn name in ISSUE_MODIFIABLE
Static parameter
The ISSUE_MODIFIABLE column value FALSE means parameters are static parameter
Static parameter values change the value in spfile only so we need to restart the database otherwise it
will be reflect on next database bounce.
Must we need to use scop=spfile
EX:
SQL>Alter system set sga_max_size=50m scope=spfile;
Dynamic parameter:
The ISSUE_MODIFIABLE column value Immediate means parameter are dynamic parameter
We can change the dynamic parameter in anytime no need to bounce for this changes
This changes are applied in database memory and spfile
We can use scope=memory and scope=both parameters
EX:
SQL> Alter system set log_archive_dest=’/u01/arch’ scope=both;
Meaning of scope= memory|spfile|both
Scope:
Changes should be made in memory spfile and both areas
Scope=Memory:
Change the parameter values in current running instance and dynamic parameters, changes are applied in memory only. No static parameter change is allowed
Scope=Spfile:
Change the parameter values in spfile only. For static and dynamic parameters, changes are recorded in the spfile. If using spfile parameter system must be restart
Scope=Both:
Change parameter values in current running instance and spfile and dynamic parameters, the change is applied in both the server parameter file and memory. No static parameter change is allowed