Hidden Parameters in Oracle Database

dbtut

6 years ago

There are many hidden parameters in Oracle databases. These parameters should be changed if required by Oracle. Unconscious change of these parameters can be a problem.

We can learn the hidden parameters and their current values with the following query.

SQL> set lines 1000
SQL> column name format a40
SQL> column value format a15
SQL> column default_val format a6
SQL> column type format a20
SQL> column description format a50
SQL>  SELECT a.ksppinm name,
         b.ksppstvl value,
         b.ksppstdf default_val,
         DECODE (a.ksppity,
                 1, 'boolean',
                 2, 'string',
                 3, 'number',
                 4, 'file',
                 a.ksppity)
            type,
         a.ksppdesc description
    FROM sys.x$ksppi a, sys.x$ksppcv b
   WHERE a.indx = b.indx AND a.ksppinm LIKE '\_%' ESCAPE '\'
ORDER BY name;

SQL> set lines 1000

SQL> column name format a40

SQL> column value format a15

SQL> column default_val format a6

SQL> column type format a20

SQL> column description format a50

SQL> SELECT a.ksppinm name,

b.ksppstvl value,

b.ksppstdf default_val,

DECODE (a.ksppity,

1, 'boolean',

2, 'string',

3, 'number',

4, 'file',

a.ksppity)

type,

a.ksppdesc description

FROM sys.x$ksppi a, sys.x$ksppcv b

WHERE a.indx = b.indx AND a.ksppinm LIKE '\_%' ESCAPE '\'

ORDER BY name;

You can change the hidden parameters as follows.

SQL> alter system set "_parameter_name"=value scope=spfile sid='*';

1	SQL> alter system set "_parameter_name"=value scope=spfile sid='*';