GOAL: Q1: How to query the hidden initialization parameters in sql ? Q2: How to change these hidden parameters? SOLUTION: A1: The hidden parameters start with an "_".They can not be viewed from the output of 'show parameter' or querying v$parameter unless and until they are set explicitly in init.ora. However, if you want to view all the hidden parameters and their default values, as well as : if they are session modifiable and system modifiable, the following query could be of help. Connected as SYSDBA, execute: SELECT a.ksppinm "Parameter", b.KSPPSTDF "Default Value", b.ksppstvl "Session Value", c.ksppstvl "Instance Value", decode(bitand(a.ksppiflg/256,1),1,'TRUE','FALSE') IS_SESSION_MODIFIABLE, decode(bitand(a.ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE','FALSE') IS_SYSTEM_MODIFIABLE 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 '/' / for finding ISPDB_MODIFIABLE : SELECT a.ksppinm "Parameter", decode(bitand(ksppiflg/524288,1),1,'TRUE','FALSE') ISPDB_MODIFIABLE FROM x$ksppi a WHERE a.ksppinm LIKE '/_clusterwide_global_transactions' escape '/' This will list all the hidden parameters with their default values. A2: Now you can change the desired parameter by setting it explicitly in init.ora or using the alter..session set command for session level. During startup, the database takes the parameters from spfile by default. So you have four options to change the parameters: While starting up the database startup with pfile, e.g. SQL> startup pfile <pfile location> Create the spfile from pfile using command "create spfile from pfile" and replace the old spfile with the new one. Use SQL> alter system set "<_parameter-of-choice>" scope=spfile and bounce the database. Use SQL> alter..session set command for session level Note: To make an underscore parameter persistent inside a 12c PDB, the same underscore parameter needs to be specified explicitly(with its default value) in cdb$root. Until they are specified in root, they are not available to be modified in PDBs since this is a dictionary operation for PDBs. NOTE: It is never recommended to modify these hidden parameters without the assistance of Oracle Support. Changing these parameters may lead to serious corruption issues, high performance degradation and other problems in the database. It is not possible to see hidden parameters of other instances in a RAC cluster.
Recent Posts
