Viewing Hidden Parameters in Oracle Database

Well they wouldn't really be 'hidden parameters' if they were out in plain sight right? Right? (crickets).

Anyway, hidden parameters won't be available through a show parameter or from v$parameter, but can be selected from several x$ks% tables. Below is a query from RedShoreTech that output's it nicely. I added several column formatting options at the top, and you can whateverother conditional statements relevant to your purpose at the end, e.g. adding 'AND a.ksppinm like '%latch%'.

set linesize 200 

col parameter for a30 col "Session Value" for a8 

col "Instance Value" for a16 

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 '/' /