Shulou(Shulou.com)06/01 Report--

This article mainly explains "detailed introduction of Oracle static parameters and dynamic parameter types". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn the "Oracle static parameters and dynamic parameter types detailed introduction" bar!

Introduction to Oracle parameter types

First, the configuration file:

Pfile (parameter file) Spfile (server configuration file) cannot dynamically modify system parameters before 8i, but since 9i, spfile provides administrators with a more convenient way to modify system parameters. the difference between the two files: pfile is a text file that can be edited through a text editor, while spfile is a binary file. It is best not to use text editing software to edit content. The system startup defaults to reading spfile.

See what file you started with:

Method 1:

SQL > select distinct ISSPECIFIED from v$spparameter

ISSPEC

-

FALSE

TRUE

If there is only FALSE, use PFILE

If you have TRUE, you are using SPFILE

Method 2:

SQL > show parameters spfile

If there is a value, use spfile to start, and vice versa pfile

If you want to create a pfile through spfile, you can use the command:

Create spfile (pfile) from pfile (spfile) (=')

You can specify either pfile or spfile at startup:

SQL > startup pfile='/home/jarodwang/my_pfile.ora'

View introduction:

The information about the parameters is saved in the view v$system_parameter.

V$SYSTEM_PARAMETER

Displays information about the initialization parameters that are currently in effect for the instance. A new session inherits parameter values from the instance-wide values.

Column

Datatypes

Description

NUM NUMBER Parameter number

NAME VARCHAR2 (64) Name of the parameter

TYPE NUMBER Parameter type

VALUE VARCHAR2 (512) Instance-wide parameter value

ISDEFAULT VARCHAR2 (9) Indicates whether the parameter is set to the default value (TRUE) or the parameter value was specified in the parameter file (FALSE)

ISSES_MODIFIABLE VARCHAR2 (5) Indicates whether the parameter can be changed with ALTER SESSION (TRUE) or not (FALSE)

ISSYS_MODIFIABLE VARCHAR2 (9) Indicates whether the parameter can be changed with ALTER SYSTEM and when the change takes effect

ISMODIFIED VARCHAR2 (8) Indicates how parameter was modified. If an ALTER SYSTEM was performed, the value will be MODIFIED

ISADJUSTED VARCHAR2 (5) Indicates whether Oracle adjusted the input value to a more suitable value (for example, the parameter value should be prime, but the user input a non-prime number, so Oracle adjusted the value to the next prime number)

DESCRIPTION VARCHAR2 (64) Description of the parameter

UPDATE_COMMENT VARCHAR2 (255) Comments associated with the most recent update

According to the issys_modifiable in v$system_parameter, you can find out which are dynamic parameters and which are static parameters. The command is as follows:

SQL > select count (*) from v$system_parameter where issys_modifiable='FALSE'

SQL > select count (*) from v$system_parameter where issys_modifiable='IMMEDIATE'

SQL > select count (*) from v$system_parameter where issys_modifiable='DEFERRED'

The above results show that the static parameters are 107 and the dynamic parameters are 144-7-151.

The scope of modification and use of the three:

Parameter type-SCOPE attribute spfile memory both deferred

Static parameters are fine, but restarting the server is not allowed.

The dynamic parameter (issys_modifiable is immediate) is OK. Restart the server can take effect, take effect immediately, restart service fails, can, take effect immediately, restart the server still has the effect but not.

The dynamic parameter (issys_modifiable is deferred) is OK, but is it possible to restart the server to take effect?

Static parameter must be specified as scope

The dynamic parameter issys_modifiable is IMMEDIATE without scope and the default is both, while the dynamic parameter issys_modifiable is DEFERRED must be added with scope=spfile or derferred.

Demo:

1. Static parameters:

SQL > alter system set processes=151

Alter system set processes=151

*

ERROR at line 1:

ORA-02095: specified initialization parameter cannot be modified

SQL > alter system set processes=151 scope=spfile

System altered.

2. Dynamic parameters (immediate)

SQL > alter system set log_archive_dest_1=' LOCATION=/u02/oradata/arch'

System altered.

3. Dynamic parameters (deferred)

Deferred specifies whether the system modification takes effect only for future sessions (invalid for the currently established session, including the session that performed the modification). By default, the ALTER SYSTEM command takes effect immediately, but some parameters cannot be modified "immediately" and can only be modified for newly established sessions.

SQL > select name from v$system_parameter where issys_modifiable='DEFERRED'

Backup_tape_io_slaves

Audit_file_dest

Object_cache_optimal_size

Object_cache_max_size_percent

Sort_area_size

Sort_area_retained_size

Olap_page_pool_size

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

SQL > alter system set sort_area_size = 65536 deferred

System altered.

At this point, I believe you have a deeper understanding of the "detailed introduction of Oracle static parameters and dynamic parameter types". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.