Get Oracle Implicit parameter information

The initialization parameters of the Oracle database are mainly derived from the two Oracle internal data dictionary tables: X$KSPPCV and X$ksppi usually we query the V$parameter view or use the show parameter command from both system tables, However, implicit parameters cannot be queried in either of these ways.

X$KSPPCV is used to record the value of the current setting and whether the default value is used, the X $KSPPI is an underlying table that records simple information such as parameter names, parameter descriptions, and so on . Note that the inst_id field is used in the RAC environment to determine the instance to which the parameter belongs.

You can query the implied parameters of the current instance using the following script:

Select  x.ksppinm  name,  y.ksppstvl  value,  y.ksppstdf  IsDefault,  decode (Bitand ( y.ksppstvf,7), 1, ' MODIFIED ', 4, ' System_mod ', ' false ')  Ismod,  decode (Bitand (y.ksppstvf,2), 2, ' TRUE ', ' false ' )  Isadjfrom  sys.x$ksppi x,  sys.x$ksppcv ywhere  x.inst_id = Userenv (' Instance ') and  Y.inst_ id = userenv (' Instance ') and  x.indx = Y.indx and  x.ksppinm like '%_&par% ' ORDER by  translate ( X.KSPPINM, ' _ ', ')/

Get Oracle Implicit parameter information