V$PARAMETER - Query Instance Parameters, Hidden & Modified Settings

V$PARAMETER

Overview

V$PARAMETER shows the current effective value of every initialization parameter for the running Oracle instance. DBAs rely on it for configuration audits, change verification after ALTER SYSTEM or ALTER SESSION commands, troubleshooting parameter-related startup failures, and comparing parameter sets across instances in RAC or Data Guard environments. Each row represents one parameter; the VALUE column reflects what is currently active in memory.

View Type: Dynamic Performance View Available Since: Oracle 7 Required Privileges: SELECT on V_$PARAMETER or SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY

Key Columns

Column	Datatype	Description
NUM	NUMBER	Internal parameter number
NAME	VARCHAR2(80)	Parameter name
TYPE	NUMBER	Parameter type: 1=Boolean, 2=String, 3=Integer, 4=Parameter file, 5=Reserved, 6=Big integer
VALUE	VARCHAR2(4000)	Current effective value in the instance
DISPLAY_VALUE	VARCHAR2(4000)	Human-readable value (e.g., “2G” instead of “2147483648”)
DEFAULT_VALUE	VARCHAR2(255)	Default value Oracle would use if parameter were not set (Oracle 12.2+)
ISDEFAULT	VARCHAR2(9)	TRUE if the current value equals the default
ISSES_MODIFIABLE	VARCHAR2(5)	TRUE if the parameter can be changed at session level (ALTER SESSION)
ISSYS_MODIFIABLE	VARCHAR2(9)	IMMEDIATE, DEFERRED, or FALSE — whether ALTER SYSTEM can modify it without restart
ISPDB_MODIFIABLE	VARCHAR2(5)	TRUE if the parameter can be set at PDB level (Oracle 12c+)
ISINSTANCE_MODIFIABLE	VARCHAR2(5)	TRUE if different RAC instances can have different values
ISMODIFIED	VARCHAR2(10)	MODIFIED if changed from spfile default at startup; SYSTEM_MOD/FALSE otherwise
ISADJUSTED	VARCHAR2(5)	TRUE if Oracle silently adjusted the value at startup
ISDEPRECATED	VARCHAR2(5)	TRUE if this parameter is deprecated
ISBASIC	VARCHAR2(5)	TRUE if this is a basic/commonly tuned parameter
DESCRIPTION	VARCHAR2(255)	Brief description of the parameter’s purpose
UPDATE_COMMENT	VARCHAR2(255)	Comment text supplied when the parameter was last changed via ALTER SYSTEM
HASH	NUMBER	Hash value of the parameter name
CON_ID	NUMBER	Container ID; 0 = CDB root, non-zero = PDB (Oracle 12c+)

Essential Queries

Basic Usage

Show all parameters that differ from their Oracle default — the most common starting point for a configuration audit:

SELECT
    NAME,
    DISPLAY_VALUE,
    DESCRIPTION
FROM
    V$PARAMETER
WHERE
    ISDEFAULT = 'FALSE'
ORDER BY
    NAME;

Monitoring Query

Report every parameter that has been modified at runtime (without a restart) so you can verify or rollback recent ALTER SYSTEM commands:

SELECT
    p.NAME,
    p.DISPLAY_VALUE                                       AS current_value,
    p.ISMODIFIED,
    p.ISSYS_MODIFIABLE,
    p.UPDATE_COMMENT,
    sp.VALUE                                              AS spfile_value
FROM
    V$PARAMETER  p
    LEFT JOIN V$SPPARAMETER sp ON sp.NAME = p.NAME
WHERE
    p.ISMODIFIED != 'FALSE'
ORDER BY
    p.NAME;

Combined with Other Views

Compare parameter values across all RAC instances simultaneously — essential in any multi-instance environment:

SELECT
    p1.NAME,
    p1.VALUE                                              AS inst1_value,
    p2.VALUE                                              AS inst2_value,
    CASE
        WHEN p1.VALUE != p2.VALUE THEN '*** MISMATCH ***'
        ELSE 'OK'
    END                                                   AS status
FROM
    (SELECT NAME, VALUE FROM GV$PARAMETER WHERE INST_ID = 1) p1
    JOIN (SELECT NAME, VALUE FROM GV$PARAMETER WHERE INST_ID = 2) p2
      ON p2.NAME = p1.NAME
WHERE
    p1.VALUE != p2.VALUE
    OR p2.VALUE IS NULL
ORDER BY
    p1.NAME;

Advanced Analysis

Generate a full parameter report for documentation or baseline comparisons, including type decoding and modifiability:

SELECT
    p.NUM,
    p.NAME,
    CASE p.TYPE
        WHEN 1 THEN 'Boolean'
        WHEN 2 THEN 'String'
        WHEN 3 THEN 'Integer'
        WHEN 4 THEN 'Parameter file'
        WHEN 6 THEN 'Big integer'
        ELSE        'Unknown (' || p.TYPE || ')'
    END                                                   AS param_type,
    p.DISPLAY_VALUE                                       AS current_value,
    p.DEFAULT_VALUE,
    p.ISDEFAULT,
    p.ISMODIFIED,
    p.ISADJUSTED,
    p.ISDEPRECATED,
    p.ISSYS_MODIFIABLE,
    p.ISSES_MODIFIABLE,
    p.ISPDB_MODIFIABLE,
    p.UPDATE_COMMENT,
    p.DESCRIPTION
FROM
    V$PARAMETER p
ORDER BY
    p.NAME;

Find deprecated parameters that should be removed from the spfile before an upgrade:

SELECT
    NAME,
    DISPLAY_VALUE,
    DESCRIPTION,
    ISMODIFIED
FROM
    V$PARAMETER
WHERE
    ISDEPRECATED = 'TRUE'
    AND ISDEFAULT  = 'FALSE'   -- only those explicitly set in spfile/pfile
ORDER BY
    NAME;

Check the current value of specific memory and cursor parameters most commonly tuned in production:

SELECT
    NAME,
    DISPLAY_VALUE,
    ISDEFAULT,
    ISSYS_MODIFIABLE,
    DESCRIPTION
FROM
    V$PARAMETER
WHERE
    NAME IN (
        'sga_target',
        'sga_max_size',
        'pga_aggregate_target',
        'pga_aggregate_limit',
        'db_cache_size',
        'shared_pool_size',
        'large_pool_size',
        'java_pool_size',
        'streams_pool_size',
        'open_cursors',
        'session_cached_cursors',
        'cursor_sharing',
        'processes',
        'sessions',
        'db_block_size',
        'undo_tablespace',
        'undo_retention',
        'log_buffer',
        'db_files',
        'control_files'
    )
ORDER BY
    NAME;

List hidden (underscore) parameters that have been explicitly set — these are Oracle internal parameters and their use should be documented:

-- Requires SELECT on X$KSPPI and X$KSPPCV (needs SYSDBA or SELECT ANY DICTIONARY)
SELECT
    ksppinm                                               AS parameter_name,
    ksppstvl                                              AS current_value,
    ksppdesc                                              AS description,
    CASE ksppstdf
        WHEN 'FALSE' THEN 'MODIFIED'
        ELSE              'DEFAULT'
    END                                                   AS is_modified
FROM
    X$KSPPI  kpi
    JOIN X$KSPPCV kpv ON kpv.INDX = kpi.INDX
WHERE
    TRANSLATE(ksppinm, '_', '#') LIKE '#%'   -- hidden parameters start with _
    AND ksppstdf = 'FALSE'                    -- only those explicitly set
ORDER BY
    ksppinm;

Common Use Cases

• Pre-upgrade parameter audit — Identify deprecated parameters and values incompatible with the target version before running the Pre-Upgrade Information Tool.
• Change verification — After applying an SR recommendation or change control, confirm ALTER SYSTEM took effect immediately vs. requiring a restart (ISMODIFIED and ISSYS_MODIFIABLE columns).
• RAC consistency checks — Parameters that are not instance-modifiable (ISINSTANCE_MODIFIABLE = FALSE) must be identical across all nodes; query GV$PARAMETER to detect drift.
• PDB parameter management — In 12c+ CDB environments, ISPDB_MODIFIABLE identifies which parameters PDB administrators can override at the container level.
• Security hardening reviews — Audit AUDIT_TRAIL, SEC_CASE_SENSITIVE_LOGON, SEC_MAX_FAILED_LOGIN_ATTEMPTS, and O7_DICTIONARY_ACCESSIBILITY in a single query.
• Documentation and baselining — Export the full non-default parameter set to a table or CSV at each change window to enable before/after comparisons.

Related Views

• V$SPPARAMETER — Shows the values stored in the server parameter file (spfile) on disk; may differ from V$PARAMETER if ALTER SYSTEM … SCOPE=SPFILE was used.
• V$SYSTEM_PARAMETER — Shows instance-level parameter values (system scope), as opposed to session-level overrides visible in V$PARAMETER.
• V$SES_OPTIMIZER_ENV — Session-level optimizer parameter overrides; complements V$PARAMETER for optimizer tuning investigations.
• GV$PARAMETER — Global version of V$PARAMETER in RAC; includes INST_ID column for per-instance filtering.

Version Notes

• Oracle 10g: DISPLAY_VALUE column added, making large integer values (e.g., SGA sizes) human-readable.
• Oracle 11g: ISBASIC column added to flag the ~30 most commonly tuned parameters.
• Oracle 12.1 (Multitenant): CON_ID and ISPDB_MODIFIABLE columns added. In a CDB, each PDB has its own effective parameter row; query from within the PDB to see PDB-specific values.
• Oracle 12.2: DEFAULT_VALUE column added, enabling direct comparison of the current value against Oracle’s compiled-in default without a separate lookup.
• Oracle 19c: Several optimizer and statistics-related parameters changed defaults (e.g., OPTIMIZER_ADAPTIVE_PLANS); always audit ISDEFAULT after an upgrade.
• Oracle 21c / 23ai: Blockchain table, JSON duality view, and True Cache parameters added to the parameter namespace. ISDEPRECATED = ‘TRUE’ for legacy parameters like LOG_ARCHIVE_START.