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>

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.