How Oracle queries initialization parameters (i) Methods for multiple query initialization parameters

Query initialization parameters A lot of ways, such as show PARAMETER, or query V$parameter, here a brief summary.

This article briefly introduces a variety of query initialization parameters methods.

There are a number of ways to initialize the setting of a parameter at first: You can only modify the memory through alter system set scope = memory, or you can modify only the SPFile value by alter system set scope = SPFile. Of course, you can also modify the settings in both SPFile and memory. There is also an initialization parameter that can set the delay to take effect, meaning that the modification will only take effect for subsequent sessions connected to the database, and that the current session and other sessions that are already connected to Oracle are not effective. Querying the database initialization parameters is even more complicated if you reconsider the RAC environment, where there are multiple startup instances in the database.

Fortunately, Oracle provides a number of query initialization parameters methods are also: Show PARAMETERS, show Spparameters, CREATE PFILE, V$parameter$parameter2, v$system_ PARAMETER, V$system_parameter2, V$spparameter.

Show parameters is a method of query initialization parameters provided by the Sqlplus tool, which is the initialization parameter for the current session.

Show Spparameters is also a method provided by the Sqlplus tool to query the initialization parameters contained in the SPFile parameters that are in effect for the current session. This command is valid after 11g Sqlplus version.

The CREATE pfile command is not as intuitive as any other method, which saves the initialization file in SPFile or in the current memory to the Pfile file, and then visually see what initialization parameters are set in SPFile or in the current memory through the text Editing tool. Although this approach looks cumbersome, the parameters listed in this method are parameters that are set by the user, and the parameters for all default values are not listed, so the results are much more intuitive than others. Versions after 11g allow the Create PFILE from MEMORY.

The V$parameter view provides settings for the initialization parameters that are visible to the current session, and if you want to query the settings of all instances of the RAC database, you can query the Gv$parameter view.

The V$parameter2 view is almost the same as V$parameter, except that for initialization parameters that include values, multiple records are returned from this view, and each record corresponds to a value. Similarly, for a RAC environment, you can query the Gv$parameter2 view.

The V$system_parameter view records the initialization parameter settings in effect for the current instance. Note that this is where the instance takes effect, not the session. Similarly, Gv$system_parameter contains initialization parameter information that is in effect for all instances.

This article URL address: http://www.bianceng.cn/database/Oracle/201410/45543.htm

The relationship between the V$system_parameter2 view and the V$system_parameter view, as well as the V$parameter2 view, is the same as the V$parameter view, which takes a branch approach for parameters that contain multiple values.

V$spparameter records initialization parameters from the SPFile file. If the parameter is not set in the SPFile file, the field isspecified corresponding value is false. You can also query the Gvspparameter parameter to display settings for all instances of the RAC environment.

First look at the difference between V$parameter and V$parameter2, the same applies to V$system_parameter and V$system_parameter2:

Sql> SELECT NAME, VALUE from V$parameter

2 minus

3 SELECT NAME, VALUE from V$parameter2;

NAME VALUE

---------------------------------------- --------------------------------------------------

Control_files e:oracleoradataytk102control01. CTL, E:oracle

Oradataytk102control02. CTL, E:oracleoradatayt

K102control03. Ctl

Sql> SELECT NAME, VALUE from V$parameter2

2 minus

3 SELECT NAME, VALUE from V$parameter;

NAME VALUE

---------------------------------------- --------------------------------------------------

Control_files e:oracleoradataytk102control01. Ctl

Control_files e:oracleoradataytk102control02. Ctl

Control_files e:oracleoradataytk102control03. Ctl