Set and Reset the Initialization Parameter Values in Oracle

Set and Reset the Initialization Parameter Values in Oracle

Initialization parameter are defined in SPFILE or PFILE of Oracle Database. It defined the value of parameter you are configured such as archive destination, archive enabled , control file location,
process value etc.

Two kinds of initialization parameters:
Dynamic Parameter: you can change when instance is working
Static Parameter: you can change only in spfile on restart it will effects.

Check the value of Parameter

--Display values set in current session
Show parameter parameter_name

--Display value set in server parameter file(SPFILE)
show spparameter parameter_name

-- View used to check parameter value
select * from v$parameter
select * from v$systemparameter

Change the value of Initialization parameter

Alter system set parameter_name=value scope=both|spfile|memory;

Note:
SCOPE = SPFILE No change in the current instance, for both dynamic and static effect seems when database restarted.
SCOPE = MEMORY Changes effect immediately in case of dynamic parameter, but it not save in SPFILE, So on reboot of database it lost.
SCOPE – BOTH Changes effect immediately in case of dynamic parameter and save in spfile, but for static it not work.

Reset the value of initialization parameter

alter system reset parameter_name;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.