Hidden Parameters in Oracle Database

dbtut April 29, 2019 ORACLE

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='*';

Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux

Hidden Parameters in Oracle Database

About dbtut

Leave a Reply Cancel reply