How to Check Oracle Hidden Parameters ?

As we know, “show parameter” doesn’t show hidden parameters values in Oracle database version before 18c. Instead, the below sql script will list hidden parameters.

SQL> show parameter report

NAME                                 TYPE        VALUE
---------------------------------- ----------- --------------
optimizer_adaptive_reporting_only    boolean     FALSE

SQL>show parameter "_report_capture_cycle_time"
SQL>

set pagesize 120
set linesize 150
col Parameter format a40
col Description format a40
col "Session Value" format a10
col "Instance Value" format a10

SELECT 
       a.ksppinm  "Parameter", 
       a.ksppdesc "Description", 
       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  '/_report_capture%'  escape '/'  
ORDER BY 1;

Parameter                                Description                              Session Va Instance V
---------------------------------------- --------------------------------
_report_capture_cycle_time               Time (in sec) between two cycles of repo 60         60
                                         rt capture daemon
_report_capture_dbtime_percent_cutoff    100X Percent of system db time daemon is 50         50
...
..
.

From 18c, it is easier to show a hidden parameter as showing a normal parameter:

SQL> show parameter allow_insert_with

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
_allow_insert_with_update_check      boolean     TRUE
SQL>