Summary of Oracle parameter viewing methods and oracle parameter Summary

Summary of Oracle parameter viewing methods and oracle parameter Summary

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

Current System Parameter

The current system parameters involve these views: v $ parameter, v $ parameter2, v $ system_parameter, v $ system_parameter2, and v $ spparameter.

V $ parameter

If the session-level parameters are not modified by "alter session", the default value is the same as the system-level parameter value. The following is the key field of the View:
1) NUM: parameter number;
2) NAME: parameter NAME;
3) TYPE: parameter TYPE:
1-Boolean;
2-String;
3-Integer;
4-Parameter file;
5-reserved;
6-Big integer;
4) VALUE: the parameter VALUE of the current session;
5) DISPLAY_VALUE: user-friendly parameter value;
6) ISSES_MODIFIABLE: true indicates that the parameter can be changed through "alter session"; false indicates that the parameter cannot be changed;
7) ISSYS_MODIFIABLE: whether the parameter can be changed by "alter system". After the parameter is changed:
IMMEDIATE: The parameter can be changed through "alter system" and takes effect immediately.
DEFERRED: The parameter can be changed through "alter system" and takes effect from 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. false indicates that all instances must have the same value. If ISSYS_MODIFIABLE is false, the value is always false;
9) ISMODIFIED: indicates whether the parameter is modified after the instance is started.
MODIFIED: the parameter is MODIFIED using "alter session ".
SYSTEM_MOD: the parameter is modified using "alter system ".
FALSE: The instance is not modified after it is started.
10) ISDEPRECATED: true indicates that the parameter is discarded; otherwise, false indicates that the parameter is used;
11) DESCRIPTION: The DESCRIPTION of the parameter;
12) UPDATE_COMMENT: the comment of the last modification;
13) HASH: the HASH value of the parameter name.

V $ system_parameter

Stores instance-level parameters. The new session inherits the parameter values. The following are the key fields of the View:
1) NUM: parameter number;
2) NAME: parameter NAME;
3) TYPE: parameter TYPE:
1-Boolean;
2-String;
3-Integer;
4-Parameter file;
5-reserved;
6-Big integer;
4) VALUE: instance-level parameter VALUE;
5) DISPLAY_VALUE: user-friendly parameter value;
6) ISSES_MODIFIABLE: true indicates that the parameter can be changed through "alter session"; false indicates that the parameter cannot be changed;
7) ISSYS_MODIFIABLE: whether the parameter can be changed by "alter system". After the parameter is changed:
IMMEDIATE: The parameter can be changed through "alter system" and takes effect immediately.
DEFERRED: The parameter can be changed through "alter system" and takes effect from 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. false indicates that all instances must have the same value. If ISSYS_MODIFIABLE is false, the value is always false;
9) ISMODIFIED: indicates how the parameter is modified. If "alter system" is executed, the value is MODIFIED;
10) ISDEPRECATED: true indicates that the parameter is discarded; otherwise, false indicates that the parameter is used;
11) DESCRIPTION: The DESCRIPTION of the parameter;
12) UPDATE_COMMENT: the comment of the last modification;
13) HASH: the HASH value of the parameter name.

V $ parameter2

Same as v $ parameter, the only difference is that if a parameter has multiple values, there will be multiple rows in v $ parameter2, and only one row in v $ parameter, use commas to separate multiple values in values.
For example, the parameter control_files is in v $ parameter:

namevalue--------------------------------------------------------------------------------------control_filesE:\ORACLE\ORADATA\LY\CONTROL01.CTL, E:\ORACLE\ORADATA\LY\CONTROL02.CTL, E:\ORACLE\ORADATA\LY\CONTROL03.CTL

In 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

This parameter is used to store the parameter information of the server parameter file (spfile). If the server parameter file is not used to start the instance, the value of the ISSPECIFIED column in each row in the view is false. The key fields of the view are as follows:
1) SID: the SID of the parameter;
2) NAME: parameter NAME;
3) VALUE: parameter VALUE (null if the server parameter file is not used to start the instance );
4) DISPLAY_VALUE: parameter value in user-friendly format;
5) ISSPECIFIED: true indicates that the parameter is specified in the server parameter file; otherwise, false indicates that;
6) ORDINAL: the location (serial number) of the parameter value (if the server configuration file is not used to start the instance, it is 0 ). It is used only when the parameter value is a list;
7) UPDATE_COMMENT: the comment of the last modification (null if the server parameter file is not used to start the instance ).

Hide Parameters

There is also a type of parameter in the Oracle system called the hidden parameter, which is used in the system but not officially published by Oracle, these parameters may be parameters that are not yet mature or used in system development. These parameters are not described in any official Oracle documentation. A common feature of their naming is that they all start, views related to hidden parameters include x $ ksppi, x $ ksppcv, and x $ ksppsv.

X $ ksppi

X $ ksppi is the basic tables of v $ parameter, v $ parameter2, v $ system_parameter, and v $ system_parameter2. The parameter information is saved. The keyword segment is as follows:
1) ADDR: Memory Address
2) INDX: serial number
3) INST_ID: instance id
4) KSPPINM: Parameter Name
5) KSPPITY: parameter type:
1-Boolean;
2-String;
3-Integer;
4-Parameter file;
6) KSPPDESC: parameter description
7) KSPPIFLG: Indicates isses_modifiable or issys_modifiable.

X $ ksppcv

Save the parameter value of the current session and associate it with x $ ksppi using indx. The keyword segment is as follows:
1) ADDR: Memory Address
2) INDX: serial number
3) INST_ID: instance id
4) KSPPSTVL: current value of the Parameter
5) KSPPSTDF: Default Value of the Parameter
6) KSPPSTVF: indicates the flag field ('modified', 'System modified', or is_adjusted)
7) KSPPSTCMNT: Comment

X $ ksppsv

Save the system parameter value and associate it with x $ ksppi with indx. The fields are basically the same as those of x $ ksppcv.

Query hidden Parameters

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

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 hidden parameters and display the parameter values of 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 '\';

Specify a specific parameter name for a. ksppinm to view the corresponding values of the specified parameter.

Obsolete Parameter

Some earlier versions of Oracle exist, but the obsolete parameters in the new version can be found in view V $ OBSOLETE_PARAMETER. The view value contains two fields, name (parameter name) and ISSPECIFIED (true indicates that the parameter is specified in the parameter file, and false indicates that no. Generally, this value should be false ).

The following describes how to query hidden parameters and ISSPECIFIED values:

SELECT name, isspecified FROM v$obsolete_parameter;

 

How to view system parameters in Oracle? Such as block unit

Three methods:
1. In the parameter file pfile, see
2. Show parameter name
3. Select * from v $ parameter;

How Does oracle view logs?

View Oracle logs

1. Oracle Log Path:

Login: sqlplus "/as sysdba"

View path: SQL> select * from v $ logfile;

SQL> select * from v $ logfile; (# log file path)

Ii. What content does the Oracle log file contain? (the number of logs may be slightly different)

Control01.ctl example01.dbf redo02.log sysaux01.dbf undotbs01.dbf

Control02.ctl redo03.log system01.dbf users01.dbf

Control03.ctl redo01.log SHTTEST. dbf temp01.dbf

Iii. How to view Oracle logs:

SQL> select * from v $ SQL (# View recent operations)

SQL> select * fromv $ sqlarea (# View recent operations)

All changes to the Oracle database are recorded in the log. Currently, the only way to analyze Oracle logs is to use the LogMiner provided by Oracle, we cannot understand the original log information at all. LogMiner is provided in later Oracle8i versions, and LogMiner is a tool that allows us to understand the log information. This tool can be used to find out logical changes to the database, detect and correct user misoperations, perform post-event audits, and analyze changes.

4. Use of LogMiner:

1. Create a data dictionary file)

1) First, add the UTL_FILE_DIR parameter in the init. ora initialization parameter file. The value of this parameter is the directory where the data dictionary file is placed on the server. For example: UTL_FILE_DIR = ($ ORACLE_HOME \ logs), restart the database to make the newly added parameters take effect:

SQL> shutdown;

SQL> startup;

2) create a data dictionary file.
SQL> connect/as sysdba
SQL> execute dbms_logmnr_d.build (dictionary_filename => 'dict. ora ', dictionary_location =>'/data1/oracle/logs ');

PL/SQL procedure successfully completed

2. Create a list of log files to be analyzed
1) Create an analysis list, that is, the log to be analyzed
SQL> execute dbms logmnr. add logfile (LogFileName => '/data1/oracle/oradata/akazamdb/redo01.log', Options => dbms_logmnr.new );
PL/SQL procedure successfully completeds
2). Add an analytic log file at a time.
SQL> execute dbms _ logmnr. add _ logfile (LogFileName => '/data1/oracle/oradata/akaz ...... the remaining full text>