How to Uncover Oracle Hidden Parameters with Views and Trace

Method One: Querying Internal Views

Oracle does not document hidden parameters in its official manuals; they are stored in internal tables. By querying the V$FIXEDVIEWDEFINITION view you can discover the definition of the public view V$PARAMETER, which in turn is built on the GV$PARAMETER view.

The GV$PARAMETER view pulls data from two internal tables: X$KSPPI and X$KSPPCV. The relevant columns are:

X$KSPPCV : records the current value, whether the default is used, memory address ( ADDR RAW(4)), index ( INDX NUMBER), instance ID, parameter name ( KSPPINM VARCHAR2(64)), and description ( KSPPDESC VARCHAR2(64)).

X$KSPPI : stores basic information such as parameter name, description, current value ( KSPPSTVL VARCHAR2(512)), default value ( KSPPSTDF VARCHAR2(9)), and a flag indicating if the value was modified ( KSPPSTVF NUMBER).

With this knowledge you can craft a query to list hidden parameters, their current values, and descriptions. For example, to find all parameters that start with _partition:

SELECT ksppinm AS parameter_name,
       ksppstvL AS current_value,
       ksppstdF AS default_value,
       ksppdesc AS description
FROM   x$ksppcv
WHERE  ksppinm LIKE '\_partition%';

The query returns rows such as _partition_max_size and _partition_min_size, showing their values and meanings.

Method Two: Using AUTOTRACE to Inspect Execution Plans

Another way to expose hidden parameters is to enable SET AUTOTRACE ON and run a simple command like SHOW PARAMETER. The generated execution plan reveals that Oracle scans the two X$ tables mentioned above.

By examining the predicate section of the plan you can see filters such as NOT LIKE '\_' that exclude parameters beginning with an underscore, i.e., hidden parameters. Combining this with the column definitions from Method One lets you construct custom queries that list both hidden and visible parameters.

Conclusion

Understanding the underlying view definitions and execution plans provides a reliable method for discovering Oracle hidden parameters, their default settings, and current values. This approach is useful for troubleshooting bugs that are resolved by toggling undocumented switches, and for gaining deeper insight into Oracle’s internal configuration mechanisms.