How to View and Set Database Parameters both Dynamic and Static (Doc ID 1480291.1)

Last updated on JULY 21, 2017

Applies to:

Oracle Database - Standard Edition - Version 9.2.0.8 to 12.1.0.1 [Release 9.2 to 12.1]
Oracle Database - Enterprise Edition - Version 9.2.0.8 to 12.1.0.1 [Release 9.2 to 12.1]
Oracle Database - Personal Edition - Version 9.2.0.8 to 12.1.0.1 [Release 9.2 to 12.1]
Information in this document applies to any platform.
***Checked for relevance on 21-Jul-2017***
There are two kinds of initialization parameters:
· Dynamic initialization parameters can be changed for the current Oracle Database instance. The changes take effect immediately.
· Static initialization parameters cannot be changed for the current instance. You must change these parameters in the text initialization file or server parameter file and then restart the database before changes take effect.

There are also two varieties of parameters:

1. Underscore parameters which are not documented in the oracle documentation set. These should not be changed unless directed to do so by oracle or unless suggested in an approved oracle publication.

2. Documented parameters which are listed in the Oracle References Manual. These may be used to tune different aspects of database configuration or performance.


This document will focus only on the published parameters.

Please refer to for information on underscore or hidden parameter usage and manipulation.

Goal

 This document is being written to provide concise information to database administrators and other interested database users on how to view current database parameter values, determine if a parameter is dynamic or static, set new values for database parameters and unset changes to database parameters.

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms