V$PARAMETER

The view reports the values of all supported parameters. It also reports any unsupported parameters with non-default values

In Oracle 12.1.0.2 V$PARAMETER has the following columns:

NameData TypeComments
NUMNUMBER
NAMEVARCHAR2(80)
TYPENUMBER
VALUEVARCHAR2(4000)
DISPLAY_VALUEVARCHAR2(4000)
DEFAULT_VALUEVARCHAR2(255)
ISDEFAULTVARCHAR2(8)
ISSES_MODIFIABLEVARCHAR2(5)
ISSYS_MODIFIABLEVARCHAR2(9)
ISPDB_MODIFIABLEVARCHAR2(5)New in 12.1
IS_INSTANCE_MODIFIABLEVARCHAR2(5)
ISMODIFIEDVARCHAR2(10)
ISADJUSTEDVARCHAR2(5)
ISDEPRECATEDVARCHAR2(5)
ISBASICVARCHAR2(5)
DESCRIPTIONVARCHAR2(256)
UPDATE_COMMENTVARCHAR2(256)
HASHNUMBER
CON_IDNUMBERContainer ID - new in 12.1

TYPE can be:

ValueDescription
1Boolean
2String
3Integer
4File
6Big Integer

The following table shows the number of supported and unsupported parameters for various Oracle releases:

Note that the above figures may vary slightly due to platform and patchset differences

V$PARAMETER does not report default values for unsupported parameters.

X$KSPPI reports values for all parameters including unsupported parameters

To dump values for all parameters use:

SELECT i.ksppinm||';'||sv.ksppstvl
FROM x$ksppi i, x$ksppsv sv
WHERE i.indx = sv.indx
ORDER BY i.ksppinm;

To set an unsupported parameter specify double quotees around the name. For example:

ALTER SESSION SET "_serial_direct_read" = NEVER;
Version Supported Unsupported Total
11.2.0.3 348 2403 2751
11.2.0.4 352 2562 2914
12.1.0.1 367 2984 3351
12.1.0.2 381 3597 3978