May 22, 2021

Setting Values in SPFILEs- Files

Once our database is up and running on the SPFILE, the next question relates to how we set and change values contained therein. Remember, SPFILEs are binary files and we can’t just edit them using a text editor. The answer is to use the ALTER SYSTEM command, which has the following syntax (portions in <> are optional, and the presence of the pipe symbol indicates “one of the list”):

Alter system set parameter=value <comment=’text’> <deferred> <scope=memory|spfile|both> <sid=’sid|*’> <container=current|all>

The ALTER SYSTEM SET command, by default, will update the currently running instance and make the change to the SPFILE for you—or in the case of a pluggable database, in the data dictionary of that pluggable database (see the following section on pluggable databases for more information). This greatly eases administration, and it eliminates the problems that arose when you used ALTER SYSTEM to add or modify parameter settings, but you forgot to update or missed an init.ora parameter file.

Let’s take a look at each element of the command:

•\    The parameter=value assignment supplies the parameter name and the new value for the parameter. For example, pga_aggregate_ target = 1024m would set the pga_aggregate_target parameter to a value of 1024MB (1GB).

•\ comment=’text’ is an optional comment you can associate with this setting of the parameter. The comment will appear in the UPDATE_ COMMENT field of the V$PARAMETER view. If you use the option to save the change to the SPFILE, the comment will be written into the SPFILE and preserved across server restarts as well, so future restarts of the database will see the comment.

•\ deferred specifies whether the system change takes place for subsequent sessions only (not currently established sessions, including the one making the change). By default, the ALTER SYSTEM command will take effect immediately, but some parameters can’t be changed immediately—they can be changed only for newly established sessions. We can use the following query to see what parameters mandate the use of deferred:

SQL> select name from v$parameter where issys_modifiable=’DEFERRED’;

NAME
backup_tape_io_slaves
recyclebin
session_cached_cursors
private_temp_table_prefix
audit_file_dest
object_cache_optimal_size
object_cache_max_size_percent
sort_area_size
sort_area_retained_size
client_statistics_level
olap_page_pool_size

Note Your results may differ; from version to version, the list of which parameters may be set online—but must be deferred—can and will change.

The code shows that SORT_AREA_SIZE is modifiable at the system level, but only in a deferred manner. The following code shows what happens if we try to modify its value with and without the deferred option:

SQL> alter system set sort_area_size = 65536; alter system set sort_area_size = 65536

ERROR at line 1:

ORA-02096: specified initialization parameter is not modifiable with this option
SQL> alter system set sort_area_size = 65536 deferred;System altered.

•\ SCOPE=MEMORY|SPFILE|BOTH indicates the “scope” of this parameter setting. Here are our choices for setting the parameter value:

•\ SCOPE=MEMORY changes the setting in the instance(s) only; it will not survive a database restart. The next time you start the database, the setting will be whatever was already recorded in the

SPFILE.

•\ SCOPE=SPFILE changes the value in the SPFILE only. The change will not take place until the database is restarted and the SPFILE is processed again. Some parameters can be changed only by using this option. For example, the processes parameter must use SCOPE=SPFILE, as you can’t change the active instance value.

•\ SCOPE=BOTH means the parameter change takes place both in memory and in the SPFILE. The change will be reflected in the current instance, and, the next time you start, this change will still be in effect. This is the default value for scope when using an SPFILE. With an init.ora parameter file, the default and only valid value is SCOPE=MEMORY. This is the default if the instance was started with an SPFILE.

•\ sid=’sid|‘ is useful mostly in a clustered environment; sid=’‘ is the default. This lets you specify a parameter setting uniquely for any given instance in the cluster. Unless you are using Oracle RAC, you will not need to specify the sid= setting.

•\ container=current|all is used in a multitenant database to determine the scope of the change. If the ALTER SYSTEM is executed in a root container database, the setting may be propagated down to every pluggable database by using the all option. Otherwise, by default, only the current container or pluggable database is affected by the change. Note that pluggable database–specific settings are not recorded in the SPFILE but are stored in the data dictionary of the pluggable database, so that when it is moved to another container, its specific settings will move with it.

A typical use of this command might be simply
SQL> alter system set pga_aggregate_target=512m; System altered.

Note The preceding command—and in fact many of the ALTER SYSTEM commands in this book—may fail on your system. If you use other settings that are incompatible with my example (other memory parameters, for example), you may well receive an error. That doesn’t mean the command doesn’t work, but, rather, the settings you attempted to use are not compatible with your overall setup.

Better yet, perhaps, would be using the COMMENT= assignment to document when and/or why a particular change was made:

SQL> alter system set pga_aggregate_target=512m comment = ‘AWR recommendation’; System altered.
SQL> select value, update_comment from v$parameter where name = ‘pga_ aggregate_target’;

VALUE UPDATE_COMMENT
536870912 AWR recommendation