Oracle DBA – A lifelong learning experience

Discrepancies in v$parameter default values in 12c

Posted by John Hallas on April 10, 2015

In my last blog about security parameters I mentioned I had found some oddities in the default values for parameters in, this is a more in-depth analysis of my findings.

Taking the parameter SEC_RETURN_SERVER_RELEASE_BANNER as an example.

Prior to 12c the default value for this parameter was ‘FALSE’, whereas the documentation for 12c ( states that the default is ‘TRUE’.

To confirm this, I made a connection to a 12c ( database and ran the following query:

select name, value,  default_value,  isdefault

from v$parameter

where name = 'sec_return_server_release_banner';
NAME                                     VALUE               DEFAULT_VALUE       ISDEFAULT
---------------------------------------- -------------------- -------------------- ---------
sec_return_server_release_banner         FALSE               TRUE                 TRUE

After confirming that the parameter had not been explicitly set in the parameter file, or as part of an alter system/session command, we could see that the actual value, held in ‘VALUE’, given to the parameter does not match the value provided by ‘DEFAULT_VALUE’ nor did it match the value it should have been assigned according to the documentation.

The next check was to see if this was a rogue parameter, or an indication of a larger problem:

select count(*) from v$parameter where isdefault = 'TRUE' and value <> default_value;



The next step was to compare the VALUE and DEFAULT_VALUE held against the values expected in the documentation and also the default values for previous versions.


Parameter: optimizer_use_pending_statistics
v$parameter.value: FALSE
12c documentation default_value: TRUE
v$parameter.default_value:  TRUE
11g default value FALSE


Parameter: read_only_open_delayed
v$parameter.value: FALSE
v$parameter.default_value: TRUE
12c documentation default_value: TRUE
11g default value FALSE



Parameter: optimizer_dynamic_sampling
v$parameter.value: 2
v$parameter.default_value: 32
12c documentation default_value: 2
11g default value 2


In all cases that were checked, the VALUE of an unaltered parameter at 12c matched that for 11g. In a number of these cases the 12c documentation incorrectly provides the wrong default value.

In some cases it appeared that the default_value had been displaced by one position in the view and that the default_value is completely inappropriate for the parameter that it has been associated with:

       NUM NAME                       VALUE                     DEFAULT_VALUE               ISDEFAULT
---------- --------------------------- ------------------------- --------------------------- ---------
     2002 undo_management             AUTO                     NULL                       TRUE
     2003 undo_tablespace             UNDOTBS1                 AUTO                       FALSE

     2564 remote_dependencies_mode   TIMESTAMP                 NULL                       TRUE
     2565 utl_file_dir                                         timestamp                   TRUE

     2741 optimizer_features_enable                 ?/rdbms/admin/sql.bsq       TRUE
     2742 fixed_date                                                    TRUE


As this previously was an database that had been upgraded to, the next check was to see if this issue was as a result of a fault in the upgrade process, or due to fundamental issues with the 12c version. So two different 12c databases were checked, both of which had been originally built as databases, and they exhibited exactly the same problems.

Document (13782248.8) on MOS covers the default_value column in v$parameter. It appears that the column was added to the v$parameter and v$system_parameter views in release as part of an enhancement request. So this is the first version of the database to hold this additional column

Next was a check to see if the definition of the v$parameter view was incorrect at this version:

select view_definition

from v$fixed_view_definition

where view_name='GV$PARAMETER'; -- v$parameter is based on gv$parameter


select x.inst_id,











decode(bitand(ksppiflg,4),4,'FALSE', decode(bitand(ksppiflg/65536,3), 0, 'FALSE', 'TRUE')),



decode(bitand(ksppilrmflg/64, 1), 1, 'TRUE', 'FALSE'),

decode(bitand(ksppilrmflg/268435456,1), 1, 'TRUE', 'FALSE'),





from x$ksppi x,

x$ksppcv y

where (x.indx = y.indx)

and bitand(ksppiflg,268435456) = 0

and ((translate(ksppinm,'_','#') not like '##%')

and ((translate(ksppinm,'_','#') not like '#%')

or (ksppstdf = 'FALSE')

or (bitand(ksppstvf,5) > 0))

So v$parameter is build up from two x$ tables, x$ksppi which holds the name and description of the parameter, and x$ksppcv which holds the actual value, default_value and isdefault information:

SQL> desc x$ksppcv
Name                                     Null?   Type
----------------------------------------- -------- ----------------------------
ADDR                                               RAW(8)
INDX                                              NUMBER   --- for joining to x$ksppi
INST_ID                                           NUMBER   --- inst_id
CON_ID                                             NUMBER   --- container id
KSPPSTVL                                          VARCHAR2(4000)   --- value
KSPPSTDVL                                         VARCHAR2(4000) --- display_value
KSPPSTDFL                                         VARCHAR2(255) --- default_value
KSPPSTDF                                          VARCHAR2(9)   --- isdefault
KSPPSTVF                                           NUMBER         --- basis for ismodified & isadjusted
KSPPSTCMNT                                         VARCHAR2(255) --- update_comment

So x$ksppcv looks a good candidate for being the culprit, so if we ignore the hidden underscore parameters:

select count(*)

from x$ksppcv a,

x$ksppi b

where a.indx = b.indx



and KSPPINM not like '\_%' escape '\';




This shows that the data loaded into this table is incorrect and matches the discrepancies found for v$parameter. Taking a single example of how the default for UTL_FILE_DIR can be ‘timestamp’ surely must demonstrate that there is an issue here.

I have raised an SR regarding this and I will update the post when I get more information, however on the face of the findings above it does seem that this is a major mess-up by Oracle .

2 Responses to “Discrepancies in v$parameter default values in 12c”

  1. John Hallas said

    There is a bug open with Oracle on this issue
    BUG 20101006 – 59534: DEFAULT VALUE SHOWN FOR “_WAIT_FOR_BUSY_SESSION” PARAMETER IS INCORRECT but it is internal only. We have been given a patch to apply but it conflicts with PSU 2 on and so we have asked for a merged patch to be created.

  2. John Hallas said

    A merge patch has now been created for but this does not completely fix the issue. There are still 48 discrepancies on our test DB.
    Oracle have now opened a new bug – 20880200 – to address the 48 parameters errors that were not fixed by the original patch.
    There is also a documentation bug 20302742 which was created in January to fix the documentation errors.

Leave a Reply

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

You are commenting using your 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: