Oracle. How to query the parameters in the current version

Oracle

The Oracle database system configures its own startup according to the parameters set in the initialization parameter file Init.ora, and each instance reads the different parameters set in these parameter files before starting. The parameters in the Oracle system can be easily divided into two categories according to the system usage, common parameters, that is, some parameters that the Oracle system normally uses, the other is special parameters, including three kinds, obsolete parameters, emphasis parameters and hidden parameters. As the new version of the Oracle database is released, some parameters are added or deleted each time. How do I query the parameters of the current version of the database system and the parameters of the current system version that are incremented or discarded relative to previous versions? This article describes in detail how to query for various parameters in the current system version.

Obsolete parameters and emphasis parameters

Oracle database, the system provides several views to view system parameters. The view v$obsolete_parameter contains all the obsolete (OBSOLETE) and accent (underscored) parameters.
Obsolete parameters (Obsolote), which exist in previous versions of Oracle but have been eliminated in the new version, are no longer used;
The emphasis parameter (underscored) refers to those parameters that are retained in the new version but are not expected to be used by the user unless special needs are made.
In view V$obsolete_parameter, contains the names of these parameters and a isspecified character that indicates whether this parameter is actually set in the Init.ora file.
The following SQL script lists all obsolete parameter names in the current system and whether they are set in the current system.
sql> COL name format A50;
sql> SELECT name, isspecified from V$obsolete_parameter;

As mentioned above, the Oracle system does not discard all parameters in the V$obsolete_parameter view, but instead converts some of them to accent parameters, and then discusses how to see if the parameters have been discarded or converted. This can be viewed through System view X$ksppo, the view contains a field named Ksppoflag that indicates whether the parameter is discarded or emphasized in the current version, or if the value is 1, which indicates that the parameter has been discarded and the value is 2, indicating that the parameter is now an emphasis parameter.
SELECT Kspponm,
DECODE (KSPPOFLG, 1, ' Obsolete ', 2, ' underscored ')
From X$ksppo
Order BY KSPPONM;

Note: This view is only visible under the SYS user.

Second, hidden parameters

The Oracle system also has a class of parameters called Hidden parameters (hidden parameters), which are used in the system but are not officially published by Oracle, which may be parameters that are not yet mature or used in system development. These parameters are not described in all Oracle official documents, and their naming has a common feature of "_" as the first character of the argument, such as _trace_files_public and _lock_sga_areas in Oracle 8i, and so on.

The following query can get all the hidden parameters in the current system (log in as SYS):
SELECT ksppinm, KSPPSTVL, Ksppdesc
From X$ksppi x, X$KSPPCV y
WHERE x.indx = Y.indx
and translate (KSPPINM, ' _ ', ' # ') like ' #% ';

Iii. Current system parameters

The following script lists all the parameters currently used by the system in English alphabetical order. In the list of parameters, if the parameter name is preceded by a symbol of #, the parameter is not explicitly specified, and the default parameter in the system is used. Typically, after a new Oracle version is installed, the script is run first, and the standard Init.ora files for that version of the database can be generated.
SET pagesize 9000
SET head off
SET term off
SELECT DECODE (IsDefault, ' TRUE ', ' # ') | |
DECODE (IsDefault, ' TRUE ', Rpad (name,43), Rpad (name,45)) | |
' = ' || Value
From V$parameter
Order BY name;

Note: The above SQL script does not list hidden parameters in the system