How to set or unset a hidden parameter

How to set or unset a hidden parameter

Summary
Displays a list of one or all the hidden parameters.
SELECT a.ksppinm AS parameter,
       a.ksppdesc AS description,
       b.ksppstvl AS session_value,
       c.ksppstvl AS instance_value
FROM   x$ksppi a,
       x$ksppcv b,
       x$ksppsv c
WHERE  a.indx = b.indx
AND    a.indx = c.indx
AND    a.ksppinm LIKE ‘/_%’ ESCAPE ‘/’
–AND    a.ksppinm = DECODE(LOWER(‘&1’), ‘all’, a.ksppinm, LOWER(‘&1’))
ORDER BY a.ksppinm;
The query returns 603 hidden parameters for Oracle9i

Display all the DESUPPORTED parameters
SELECT kspponm NAME,
       DECODE(ksppoflg,1,’DESUPPORTED’,’UNDERSCORE’) SETTING
FROM   x$ksppo
WHERE ksppoflg = 1
ORDER BY NAME ASC;

To set a hidden parameter issue
ALTER SYSTEM SET “_log_io_size”= 1048576 SCOPE = SPFILE;
To unset a parameter issue
ALTER SYSTEM RESET db_block_buffers SCOPE=SPFILE SID=’*’;
To see which hidden parameters have been set issue
SELECT * FROM v$parameter WHERE SUBSTR(NAME, 0,1) =’_’;

  • September 27, 2018 | 19 views
  • Comments