Blog

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

Follow me

Contact us for Training/ Job Support

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.