Files - Expert Oracle Database Architecture - page 84

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 .

•

•

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

EODA@ORA12CR1> alter system set pga_aggregate_target=512m;
System altered.

■ the preceding command—and in fact many of the ALTER SYSTEM commands in this topic—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.

Note

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

EODA@ORA12CR1> alter system set pga_aggregate_target=512m
2 comment = 'Changed 14-aug-2013, AWR recommendation';

System altered.

EODA@ORA12CR1> select value, update_comment
2 from v$parameter
3 where name = 'pga_aggregate_target'
4 /