Summary of Oracle parameter viewing methods

Oracle provides a number of system parameters, the following is a summary of query methods.

System Current Parameters

The current parameters of the system relate to these views: V$parameter, V$parameter2, V$system_parameter, V$system_parameter2, and V$spparameter.

V$parameter

The parameters of the session level are stored, and if not modified by ALTER session, the default and system-level parameter values are the same, and the following are the key fields of the view:
1) NUM: parameter number;
2) Name: The name of the parameter;
3) Type: Parameter type:
1-boolean;
2-string;
3-integer;
4-parameter file;
5-Reservations;
6-big integer;
4) Value: The parameter value of the current session;
5) Display_value: User-friendly parameter values;
6) isses_modifiable:true indicates that the parameter can be changed by "Alter session", FALSE indicates that it cannot be changed;
7) Issys_modifiable: Whether the parameter can be changed by "alter system" after change:
IMMEDIATE: Parameters can be changed by "alter system", effective immediately
DEFERRED: Parameters can be changed by "alter system" and will take effect at the next session
False indicates that it cannot be changed;
8) isinstance_modifiable:true indicates that the parameter values can be different under each instance, and false means that all instances must have the same value. If Issys_modifiable is false, the value is always false;
9) IsModified: Indicates whether the parameter was modified after the instance was started
MODIFIED: Parameters are modified using "alter session"
System_mod: Parameters are modified using "alter SYSTEM"
FALSE: Has not been modified since the instance was started
Isdeprecated:true indicates that the parameter is deprecated, otherwise false;
One) DESCRIPTION: Description information of the parameters;
Update_comment: the last modified comment;
Hashes: The hash value of the parameter name.

V$system_parameter

Holds the instance-level parameters, and the new session inherits the parameter values from here. Here is the key field for the view:
1) NUM: parameter number;
2) Name: The name of the parameter;
3) Type: Parameter type:
1-boolean;
2-string;
3-integer;
4-parameter file;
5-Reservations;
6-big integer;
4) Value: The parameter value of the instance level;
5) Display_value: User-friendly parameter values;
6) isses_modifiable:true indicates that the parameter can be changed by "Alter session", FALSE indicates that it cannot be changed;
7) Issys_modifiable: Whether the parameter can be changed by "alter system" after change:
IMMEDIATE: Parameters can be changed by "alter system", effective immediately
DEFERRED: Parameters can be changed by "alter system" and will take effect at the next session
False indicates that it cannot be changed;
8) isinstance_modifiable:true indicates that the parameter values can be different under each instance, and false means that all instances must have the same value. If Issys_modifiable is false, the value is always false;
9) IsModified: Indicates how the parameter was modified. If "alter system" is executed, this value will be modified;
Isdeprecated:true indicates that the parameter is deprecated, otherwise false;
One) DESCRIPTION: Description information of the parameters;
Update_comment: the last modified comment;
Hashes: The hash value of the parameter name.

V$parameter2

As with V$parameter, the only difference is that if one parameter has multiple values, there will be multiple rows in V$parameter2, and only one row in V$parameter, with commas separating multiple values in value.
For example, the parameter control_files, in V$parameter:

Namevalue--------------------------------------------------------------------------------------Control_filese : \oracle\oradata\ly\control01. CTL, E:\ORACLE\ORADATA\LY\CONTROL02. CTL, E:\ORACLE\ORADATA\LY\CONTROL03. Ctl

In the V$parameter2:

Namevalue--------------------------------------------------------------------------------------Control_filese : \oracle\oradata\ly\control01. Ctlcontrol_filese:\oracle\oradata\ly\control02. Ctlcontrol_filese:\oracle\oradata\ly\control03. Ctl

V$system_parameter2

Similar to V$parameter2.

V$spparameter

For parameter information that holds the server parameter file (spfile), if the server parameter file is not used to launch the instance, the value of the Isspecified column for each row of the view is false, and the key field for the view is as follows:
1) SID: Sid of the parameter;
2) Name: The name of the parameter;
3) Value: The value of the parameter (or null if the server parameter file is not used to launch the instance);
4) Display_value: parameter value, using user-friendly format;
5) isspecified:true indicates that the parameter is specified in the server parameter file, otherwise false;
6) ORDINAL: The position (ordinal) of the parameter value (0 if the server-side profile is not used to launch the instance). Used only if the parameter value is a list;
7) Update_comment: A comment that was last modified (or null if the server parameter file was not used to launch the instance).

Hide Parameters

The Oracle system also has a class of parameters called Hidden parameters (hidden parameters), which are used in the system, but are not publicly disclosed by Oracle, which may be parameters that are not yet mature or are used in system development. These parameters are not documented in all Oracle-supplied documents, and their naming has a common feature of ' _ ' as the first character of the parameter, and the views associated with the hidden parameters are X$ksppi, X$KSPPCV, and X$KSPPSV.

X$ksppi

X$ksppi is the base table for V$parameter, V$parameter2, V$system_parameter, and V$system_parameter2, and the key fields for saving parameter information are as follows:
1) ADDR: Memory address
2) INDX: Serial number
3) inst_id: instance number
4) KSPPINM: Parameter name
5) Ksppity: Parameter type:
1-boolean;
2-string;
3-integer;
4-parameter file;
6) Ksppdesc: Parameter description information
7) KSPPIFLG: Flag, used to illustrate isses_modifiable or issys_modifiable

X$ksppcv

Save the parameter values of the current session, and X$ksppi with Indx, the key fields are as follows:
1) ADDR: Memory address
2) INDX: Serial number
3) inst_id: instance number
4) KSPPSTVL: The current value of the parameter
5) KSPPSTDF: Default value of Parameter
6) KSPPSTVF: Flag field for description (' Modified ', ' System Modified ' or is_adjusted)
7) Ksppstcmnt: Notes

X$ksppsv

Save system parameter values, and X$ksppi are associated with indx, fields and X$KSPPCV are basically the same.

Query Hidden parameters

Query the parameter values and default values for hidden parameters and current session:

Select Ksppinm "Parameter Name", KSPPSTVL "Value", KSPPSTDF "Default" from  X$ksppi x, x$ksppcv y where x.indx = Y.indx   and ksppinm like '/_%trace% ' escape '/';

View the hidden parameters and display the parameter values for the current session and instance:

Select A.ksppinm  Parameter,       a.ksppdesc Description,       b.ksppstvl "Session Value",       C.KSPPSTVL "  Instance Value "from  X$ksppi A, X$KSPPCV B, x$ksppsv c where a.indx = B.indx and   a.indx = C.indx   and A.KSPPINM Like ' \_% ' escape ' \ ';

You can view the corresponding value for the specified parameter by specifying the A.KSPPINM with a specific argument name.

Deprecated parameters

There are some previous versions in Oracle that have obsolete parameters in the new version, which can be found in view V$obsolete_parameter, which contains two fields, name (parameter name), and Isspecified (true indicates that the parameter is specified in the parameter file , False indicates No. In general, this value should be false).

Here's how to query for hidden parameters and isspecified values:

SELECT name, isspecified from V$obsolete_parameter;

Summary of Oracle parameter viewing methods