Check hidden and default parameter value in Oracle

Hidden parameter in oracle is started with first character “_” underscore . These parameter are set according to system configuration during setup the oracle database. Oracle define the hidden parameter at instance and db level. Oracle do not recommend to changes this hidden parameter.

List all hidden parameter with value and description

SELECT 
    a.ksppinm AS parameter_name,
    b.ksppstvl AS parameter_value,
    a.ksppdesc AS description
FROM 
    x$ksppi a, 
    x$ksppcv b
WHERE 
    a.indx = b.indx
    AND a.ksppinm LIKE '%<parameter_name>%';

Alter or Change the Value of Hidden parameter

ALTER SYSTEM SET "_your_parameter_name" = 'new_value' SCOPE = BOTH;

Check the default parameters in Oracle

SELECT 
    name,
    value,
    isdefault,
    isspecified,
    description
FROM 
    v$parameter
WHERE 
    isdefault = 'TRUE'