Tuesday, June 5, 2012

How to display all oracle hidden parameters

Oracle has many hidden parameters.
You will not find them in V$PARAMETER or see them with SHOW PARAMETERS command as these are hidden.
All these parameter start with _ (Underscore).

SELECT NAME, VALUE, DECODE (isdefault, 'TRUE', 'Y', 'N') AS "Default",
DECODE (isem, 'TRUE', 'Y', 'N') AS sesmod,
DECODE (isym,'IMMEDIATE', 'I','DEFERRED', 'D','FALSE', 'N') AS sysmod,
DECODE (imod,'MODIFIED', 'U','SYS_MODIFIED', 'S','N') AS modified,
DECODE (iadj, 'TRUE', 'Y', 'N') AS adjusted,
description
FROM ( --GV$SYSTEM_PARAMETER
SELECT x.inst_id AS INSTANCE, x.indx + 1, ksppinm AS NAME, ksppity,
ksppstvl AS VALUE, ksppstdf AS isdefault,
DECODE (BITAND (ksppiflg / 256, 1),1, 'TRUE','FALSE') AS isem,
DECODE (BITAND (ksppiflg / 65536, 3),1, 'IMMEDIATE',2, 'DEFERRED','FALSE') AS isym,
DECODE (BITAND (ksppstvf, 7),1, 'MODIFIED','FALSE') AS imod,
DECODE (BITAND (ksppstvf, 2), 2, 'TRUE', 'FALSE') AS iadj,
ksppdesc AS description
FROM x$ksppi x, x$ksppsv y
WHERE x.indx = y.indx
AND SUBSTR (ksppinm, 1, 1) = '_'
AND x.inst_id = USERENV ('Instance'))
ORDER BY NAME;

No comments: