Measure the test taker's knowledge about system parameters in Oracle databases.

The Oracle database system configures its own startup according to the parameters set in the init. ora file of the initialization parameter file. Each instance first reads the different parameters set in these parameter files before it starts. Parameters in the Oracle system can be divided into two categories according to the system usage. Common parameters are some of the parameters that the Oracle system uses normally. The other type is special parameters, including three types, obsolete parameters, emphasizing parameters, and hiding parameters. With the release of the new version of the Oracle database, parameters are added or deleted each time. How can I query the parameters of the database system of the current version and the parameters that the current system version has added or dropped relative to the previous version? This article describes in detail how to query parameters in the current system version.

I. Obsolete parameters and emphasized Parameters

In Oracle databases, the system provides several views to view system parameters. View v $ obsolete_parameter contains all the obsolete (obsolete) and stressed (underscored) parameters. Here, we will first describe what is Oracle's obsolote and underscored parameters, which, as the name suggests, exist in earlier versions of oracle, however, parameters that have been eliminated in the new version are no longer used. parameters that have been retained in the new version, unless otherwise required. In view v $ obsolete_parameter, the parameter name and a flag isspecified are included to indicate whether the parameter has been set in the init. ora file.

The following SQL script lists all outdated 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 the parameters in the V $ obsolete_parameter view, but converts some of them into emphasized parameters, next we will discuss how to check whether these parameters have been discarded or converted. This can be viewed through the System View x $ ksppo. This view contains a field named ksppoflag, which indicates whether the parameter is discarded or emphasized in the current version, if the value is 1, it indicates that the parameter has been discarded. If the value is 2, it indicates that the parameter is now an stressed 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.

Ii. 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, such as _ trace_files_public and _ lock_sga_areas in Oracle 8i.

The following query shows all the hidden parameters in the current system (Log On 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 alphabetical order. In the listed parameters, if the parameter name is preceded by the # symbol, it indicates that the parameter is not explicitly specified and uses the default parameter in the system. Generally, after a new Oracle version is installed, run the script first to generate the standard init. ora file for the database version.

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 preceding SQL script does not list hidden parameters in the system.