Oracle Database - (Initialization|System) Parameters

Card Puncher Data Processing

About

Parameter are global variable.

Their values are initialized from a file during the start of the Oracle Database.

If you start a database instance using spfile with an environment variable set, then its value is automatically stored in spfile. If you unset the environment variable subsequently and start the instance afresh, then database uses the parameter value of Oracle base stored in spfile.

Oracle Database Standard Installation Parameters

Property

Scope

The SCOPE of a parameter Scope depends on whether you started up the database using a client-side parameter file (pfile) or server parameter file (spfile).

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.

MEMORY

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.

Example

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;

Changeable

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

Not changeable online

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

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...

Parameter Management

Update

for the system

The server parameter file enables you to change initialization parameters with ALTER SYSTEM commands, and to carry the changes across a shutdown and startup.

Three ways:

  • By editing an initialization parameter file
  • By issuing an ALTER SYSTEM SET … SCOPE=SPFILE statement to update a server parameter file
  • By issuing an ALTER SYSTEM RESET … SCOPE=SPFILE statement to remove a parameter from a server parameter file, causing the default value to take effect the next time you start an instance of the database.

Example:

ALTER SYSTEM RESET "_optimizer_cartesian_enabled" scope=SPFILE sid='*';

for a session

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD'

See

select * from v$parameter:

Documentation / Reference





Discover More
Card Puncher Data Processing
Oracle - Controlling the Behavior of the Query Optimizer with initialization parameters

This section lists some initialization parameters that can be used to control the behaviour of the query optimizer. These parameters can be used to enable various optimizer features in order to improve...
Card Puncher Data Processing
Oracle - Date Datatype

Use the DATE data type to store point-in-time values (dates and times) in a table. An application that specifies the time for a job might use the DATE data type. The DATE data type stores: the century,...
Card Puncher Data Processing
Oracle Database

Documentation about the Oracle database
Card Puncher Data Processing
Oracle Database - (Degree|Degree of Parallelism (DOP))

Oracle's parallel execution framework enables you to either explicitly chose - or even enforce - a specific degree of parallelism (DOP) or to rely on Oracle to control it. A degree of 1 is the default...
Card Puncher Data Processing
Oracle Database - (initialization|server) parameter file (init.ora | spfile.ora)

How to manage the initialization parameters when the database start. By default, the preconfigured database uses a parameter file (spfile) named spfilesid.ora, which is stored in the $ORACLE_HOME/dbs...
Card Puncher Data Processing
Oracle Database - Adaptive Optimization

Adaptive Optimization includes the following functionalities: adaptive plan automatic reoptimization duringautomatic reoptimizationafter When the OPTIMIZER_FEATURES_ENABLE initialization...
Card Puncher Data Processing
Oracle Database - Background Process

Persistent processes that make up an instance. This background processes are started from Linux/Windows with the help of only one binary executable program named oracle. The background process are then...
Card Puncher Data Processing
Oracle Database - CPU_COUNT parameter

CPU_COUNT specifies the number of CPUs core (processor) available for Oracle Database to use. CPU count is an automatically derived parameter by the Oracle system and is used to determine: the default...
Card Puncher Data Processing
Oracle Database - DB_BLOCK_SIZE initialization parameter

Value : 16K of 32K ( 1024) This parameter in the init.ora is the most important. This can be done only during creation time. If you have already created the Database you cannot change this value. You...
Card Puncher Data Processing
Oracle Database - DB_DOMAIN

The DB_NAME initialisation parameters determines the local name component of the database's name, while the DB_DOMAIN parameter indicates the domain (logical location) within a network structure. The combination...



Share this page:
Follow us:
Task Runner