Oracle Database - Alter System Statement

You can change any parameters for the start of the database with the “alter system” command.

The SCOPE clause lets you specify when the change takes effect. Scope depends on whether you started up the database using a client-side parameter file (pfile) or server parameter file (spfile).

• MEMORY MEMORY indicates that the change is made in memory, takes effect immediately, and persists until the database is shut down. If you started up the database using a parameter file (pfile), then this is the only scope you can specify.
• SPFILE SPFILE indicates that the change is made in the server parameter file. The new setting takes effect when the database is next shut down and started up again. You must specify SPFILE when changing the value of a static parameter that is described as not modifiable in Oracle Database Reference.
• BOTH BOTH indicates that the change is made in memory and in the server parameter file. The new setting takes effect immediately and persists after the database is shut down and started up again.

If a server parameter file was used to start up the database, then BOTH is the default. If a parameter file was used to start up the database, then MEMORY is the default, as well as the only scope you can specify.

ALTER system SET db_2k_cache_size=100m SCOPE=SPFILE;
ALTER system SET db_2k_cache_size=100m SCOPE=MEMORY;
ALTER system SET db_2k_cache_size=100m SCOPE=BOTH;

Not every parameter is changeable when the instance is running; parameters fall into three general categories:

• Not changeable online. The Oracle Database Reference describes initialization parameters and their properties, among other things. The documentation includes a “modifiable” property for each parameter, and if a parameter is not modifiable, it is not changeable online. AUDIT_TRAIL, for example, is not modifiable (not changeable online).
• Changeable online, but only for future sessions. The change won’t affect any currently connected session, but it will affect all new sessions created after the ALTER SYSTEM was executed. For example, SORT_AREA_SIZE is changeable online, but only for future sessions:

SQL> ALTER system 
  2    SET sort_area_size =32765 
  3    deferred scope=memory;
System altered.
 
SQL> SHOW parameter sort_area_size
 
NAME             TYPE      VALUE
-------          -------   -----
sort_area_size   integer   65536
 
SQL> connect /
Connected.
SQL> SHOW parameter sort_area_size
 
NAME             TYPE      VALUE
-------          -------   -----
sort_area_size   integer   32765

• Changeable online and immediately reflected in all sessions. The change will connect all currently connected sessions. For example, USER_DUMP_DEST is changeable online and is immediately reflected in all sessions:

SQL> SHOW parameter user_dump_dest
 
NAME             TYPE      VALUE
-------          -------   -----
user_dump_dest   string   /tmp
 
SQL> ALTER system 
SET user_dump_dest = 
'/home/ora10gr2/rdbms/log';
System altered.
 
SQL> SHOW parameter user_dump_dest
 
NAME             TYPE      VALUE
-------          -------   -----
user_dump_dest   string   /home/ora10...

• On Wrong and Right By Tom Kyte Oracle ACE