How to View and Set Dynamic and Static Oracle Database Parameters

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:

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.
Documented parameters which are listed in the Oracle References Manual. These may be used to tune different aspects of database configuration or performance.

TO VIEW CURRENT PARAMETER SETTINGS

It is easiest to use the show parameter syntax to view current parameter settings. For example:

SQL> show parameter sga

SQL> show parameter sga

NAME                                 TYPE         VALUE
------------------------------------ ------------ -----------------------------
lock_sga                             boolean      FALSE
pre_page_sga                         boolean      FALSE
sga_max_size                         big integer  292M
sga_target                           big integer  292M
SQL>

Note: This returns all parameter values that include the partial name provided or you can specify the full name of the parameter to get a single value.

SQL> show parameter sga_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_target                           big integer 292M
SQL>

TO DETERMINE IF THE PARAMETER IS DYNAMIC OR STATIC

You may use the following query from v$parameter to show the current session values and display whether the parameter is dynamic or static:

SQL> set linesize 62
SQL> col NAME format a40
SQL> colsession_mod format a11
SQL> colsystem_mod format a11

SQL> Select name, ISSES_MODIFIABLE session_mod, ISSYS_MODIFIABLE system_mod from v$parameter where name='sga_target';

SQL> Select name, ISSES_MODIFIABLE session_mod, ISSYS_MODIFIABLE system_mod from v$parameter where name='sga_target';
NAME                                     SESSION_MOD SYSTEM_MOD
---------------------------------------- ----------- -----------
sga_target                               FALSE       IMMEDIATE

SQL> Select name, ISSES_MODIFIABLE session_mod, ISSYS_MODIFIABLE system_mod from v$parameter where name='sga_max_size';

NAME                                     SESSION_MOD SYSTEM_MOD
---------------------------------------- ----------- -----------
sga_max_size                             FALSE       FALSE

Note: Please note that the first query shows that sga_target cannot be changed at the session level with the value of false but can be changed dynamically at the system level with the value of immediate. The parameter sga_max_size cannot be changed at the session nor at the system level dynamically. Please note that the parameter names are in lower case for purposes of searching

example:

SQL> Select name, ISSES_MODIFIABLE session_mod, ISSYS_MODIFIABLE system_mod from v$parameter where name='SGA_TARGET';

no rows selected

Note: Uppercase names will return no information.

To Change a Database Parameter

There are three options here:

1. Session modifiable dynamic - alter session set:

SQL> alter session set sql_trace=true;

Session altered.

SQL> alter session set sql_trace=false;

Session altered.

2. System modifiable dynamic - alter system set:

SQL> alter system set  sql_trace=true;

System altered.

SQL> alter system set sql_trace=false;

System altered.

Please note that the reset command can also be used but must be used with the scope=spfile or an error will be reported.

3. Use the reset command to set the parameter to the default value.

SQL> alter system reset sql_trace;
alter system reset sql_trace
*
ERROR at line 1:
ORA-32029: resetting with SCOPE=MEMORY or SCOPE=BOTH is
currently not supported

The instance will need to be shutdown and restarted for the reset parameters to take effect from the spfile. Remember we got this error when trying to reset the parameter incorrectly:

SQL> alter system reset sql_trace;
alter system reset sql_trace
*
ERROR at line 1:
ORA-32029: resetting with SCOPE=MEMORY or SCOPE=BOTH is
currently not supported

SQL> create spfile from pfile;  (to have a pfile to use on startup and make online changes to parameters)
SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area  304807936 bytes
Fixed Size                  2227864 bytes
Variable Size             109052264 bytes
Database Buffers          188743680 bytes
Redo Buffers                4784128 bytes
Database mounted.
Database opened.

SQL> alter system set sql_trace=true scope=spfile;

System altered.

SQL> alter system reset sql_trace scope=spfile;

System altered.

Static parameters only take effect after the instance is shutdown and restarted. For example:

SQL> alter system set sga_max_size=500M scope=spfile;

System altered.

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area  521936896 bytes
Fixed Size                  2229944 bytes
Variable Size             327158088 bytes
Database Buffers          184549376 bytes
Redo Buffers                7999488 bytes
Database mounted.
Database opened.

SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 500M
sga_target                           big integer 292M

SQL> alter system set sga_target=400M;

System altered.

SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 500M
sga_target                           big integer 400M

Note: We have here the example of a static and dynamic parameter change made with the alter system command. We had to shutdown and restart the instance for the static parameter sga_max_size to take effect. The dynamic parameter sga_target was changed in memory as well as in the spfile as we can see by doing a shutdown and startup.

SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 500M
sga_target                           big integer 400M

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area  521936896 bytes
Fixed Size                  2229944 bytes
Variable Size             226494792 bytes
Database Buffers          285212672 bytes
Redo Buffers                7999488 bytes
Database mounted.
Database opened.

SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 500M
sga_target                           big integer 400M

For 12c

1. Using the ALTER SYSTEM Statement on a PDB

The current user must be granted the following privileges, and the privileges must be either commonly granted or locally granted in the PDB:

CREATE SESSION
ALTER SYSTEM

To use ALTER SYSTEM to modify a PDB - This ALTER SYSTEM statement sets the STATISTICS_LEVEL initialization parameter to ALL for the current PDB:

SQL> ALTER SYSTEM SET STATISTICS_LEVEL = ALL ;

2. Using the ALTER SYSTEM SET Statement in a CDB

When the current container is the root, the CONTAINER clause of the ALTER SYSTEM SET statement controls which PDBs inherit the parameter value being set. The CONTAINER clause has the following syntax:

ALTER SYSTEM SET [PARAMETER] CONTAINER = { CURRENT | ALL };

The following settings are possible :

- CURRENT, the default, means that the parameter setting applies only to the current container. When the current container is the root, the parameter setting applies to the root and to any PDB with an inheritance property of true for the parameter.

- The following query returns the current container name:

SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL;

- The following query returns the All containers with its status:

SELECT NAME,OPEN_MODE FROM V$CONTAINERS;

- To list the initialization parameters that are modifiable in PDB:

SELECT NAME FROM V$SYSTEM_PARAMETER WHERE ISPDB_MODIFIABLE='TRUE' ORDER BY NAME;

- ALL means that the parameter setting applies to all containers in the CDB, including the root and all of the PDBs. Specifying ALL sets the inheritance property to true for the parameter in all PDBs.

This ALTER SYSTEM SET statement sets the OPEN_CURSORS initialization parameter to 200 for the all containers and sets the inheritance property to TRUE in each PDB.

ALTER SYSTEM SET OPEN_CURSORS = 200 CONTAINER = ALL;