Oracle: Hidden parameters

Init parameters whose name start with an underscore are so-called hidden parameters.

In order to modify a hidden parameter, the name of the parameter needs to be enclosed in apostrophes.

Unless a hidden parameter is explicitely set to its default value in CDB$ROOT, this parameter is not available in a PDB (because this is a dictionary operation in a PDB).

Querying the value of a hidden parameter

Hidden parameters are not listed in v$parameter and by extension not shown with the show parameter command in SQL*Plus.

However, it is possible two query hidden parameters from x$ksppi when joined to x$ksppcv and x$ksppsv.

Alternatively, the session value of a hidden parameter can be obtained with

select
   sys.dbms_stats_internal.get_parameter_val('_adaptive_fetch_enabled')
from
   dual;