Setting Values in SPFILEs in Objective-C

Generator DataMatrix in Objective-C Setting Values in SPFILEs

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|*'> The ALTER SYSTEM SET command, by default, will update the currently running instance and make the change to the SPFILE for you. 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 1,024MB (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:

ops$tkyte%ORA11GR2> select name 2 from v$parameter 3 where issys_modifiable='DEFERRED'; NAME -----------------------------backup_tape_io_slaves recyclebin audit_file_dest object_cache_optimal_size

object_cache_max_size_percent sort_area_size sort_area_retained_size olap_page_pool_size 8 rows selected.

Note Your results may differ; from version to version, the list of which parameters may be set online but must

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: ops$tkyte%ORA11GR2> 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 ops$tkyte%ORA11GR2> 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.

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.