2 SHOWS SEVERAL EXAMPLES.THE FIRST QUERY IN FIGURE 2 SHOWS THAT TH...

3-2 shows several examples.

The first query in Figure 3-2 shows that the values for the parameter DB_CREATE_

FILE_DEST are the same in the running instance in memory, and in the spfile on disk.

The next two commands adjust the parameter in both places to different values, by

using the SCOPE keyword. The results are seen in the second query. The final command

uses SCOPE=BOTH to change both the running and the stored value with one

command. The BOTH option is the default, if the SCOPE keyword is not specified.

Figure 3-1 Initialization parameters, as seen through Database Control

PA R T I

Figure 3-2 Changing and querying parameters with SQL*Plus

EXAM TIP An attempt to change a static parameter will fail unless the

SCOPE is specified as SPFILE. The default SCOPE is BOTH the running

instance and the spfile. If the instance is started with a pfile, then

SCOPE=SPFILE will fail.

As was seen in Chapter 2, when a database instance is first created, it is built with

a pfile. This may be converted to an spfile using this command:

create spfile [='spfilename'] from pfile [='pfilename'];

If names are not given for spfilename or pfilename, then the default names based on

the ORACLE_HOME and the SID will be assumed. To reverse-engineer an spfile into a

pfile, the command is

create pfile [='pfilename'] from spfile [='spfilename'] ;

The CREATE PFILE and CREATE SPFILE commands can be run from SQL*Plus at

any time, even before the instance has been started.

The Basic Parameters

The instance parameters considered to be “basic” are those that should be considered

for every database. In some cases, the default values will be fine—but it is good

practice to always consider the values of the basic parameters in your database. The

basic parameters and their current values may be queried using

select name,value from v$parameter where isbasic='TRUE' order by name;

A query that may give slightly different results is

select s.name,s.valuefrom v$spparameter s join v$parameter p on s.name=p.namewhere p.isbasic='TRUE' order by name;OCA/OCP Oracle Database 11g All-in-One Exam Guide

104

Any differences are because some parameter changes may have been applied to

the instance but not the spfile (or vice versa). The necessity for the join is because

there is no column on V$SPPARAMETER to show whether a parameter is basic or

advanced. Table 3-1 summarizes the basic parameters.

Parameter Purposecluster_database Is the database a RAC or a single instance? That this is basic indicates that RAC is considered a standard optioncompatible The version that the instance will emulate. Normally this would be the actual version, but it can look like older versionscontrol_files The name and location of the controlfile copiesdb_block_size The default block size for formatting datafilesdb_create_file_dest The default location for datafilesdb_create_online_log_dest_1 The default location for online redo logfilesdb_create_online_log_dest_2 The default location for online redo logfiles multiplexed copiesdb_domain The domain name that can be suffixed to the db_name to generate a globally unique namedb_name The name of the database (the only parameter with no default)db_recovery_file_dest The location of the flash recovery areadb_recovery_file_dest_size The amount of data that may be written to the flash recovery areadb_unique_name A unique identifier necessary if two databases with the same db_name are on the same machineinstance_number Used to distinguish two or more RAC instances opening the same database. Another indication that RAC is considered standardjob_queue_processes The number of processes available to run scheduled jobslog_archive_dest_1 The destination for archiving redo logfileslog_archive_dest_2 The destination for multiplexed copies of archived redo logfileslog_archive_dest_state_1 An indicator for whether the destination is enabled or notlog_archive_dest_state_2 An indicator for whether the destination is enabled or notnls_language The language of the instance (provides many default formats)nls_territory The geographical location of the instance (which provides even more default formats)open_cursors The number of SQL work areas that a session can have open at oncepga_aggregate_target The total amount of memory the instance can allocate to PGAsprocesses The maximum number of processes (including session server processes) allowed to connect to the instanceTable 3-1 The Basic Parametersremote_listener The addresses of listeners on other machines with which the instance should register; another parameter that is only relevant for a RACremote_login_passwordfile Whether or not to use an external password file, to permit password file authenticationrollback_segments Almost deprecated—superseded by the UNDO parameters that followsessions The maximum number of sessions allowed to connect to the instancesga_target The size of the SGA, within which Oracle will manage the various SGA memory structuresshared_servers The number of shared server processes to launch, for sessions that are not established with dedicated server processesstar_transformation_enabled Whether to permit the optimizer to rewrite queries that join the dimensions of a fact tableundo_management Whether undo data should be automatically managed in an undo tablespace, or manually managed in rollback segmentsundo_tablespace If using automatic undo management, where the undo data should resideTable 3-1 The Basic Parameters (continued)

All of these basic parameters, as well as some of the advanced parameters, are

discussed in the appropriate chapters.

Changing Parameters

The static parameters can only be changed using an ALTER SYSTEM command with a

SCOPE=SPFILE clause. Remember this command updates the spfile. Static parameters

cannot, by definition, take immediate effect. An example of a static parameter is

LOG_BUFFER. If you want to resize the log buffer to 6MB, you may issue the

command:

alter system set log_buffer=6m;

It will fail with the message “ORA-02095: specified initialization parameter cannot be

modified.” It must be changed with the SCOPE=SPFILE clause. The command will

succeed, but the instance must be restarted for the new value to take effect.

TIP The default log buffer size is probably correct. If you raise it, you may find

that commit processing takes longer. If you make it smaller than its default value,

it will in fact be internally adjusted up to the default size.