Query to get the undocumented parameters and their values

I am posting this to have a handy reference, as x$ tables are greek to me and I am not aware of another way to do this other than query the x$ tables.

Here is a query to display all hidden or undocumented parameters and their default values.

SELECT
a.ksppinm "Parameter",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
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 '/'
/

When you run this query, expect to see hundreds of rows where the undocumented parameters start with an “_”. Hence you’d probably want to filter this by providing the parameter of interest in the LIKE clause of the query.