LLI Wiki — My Digital Knowledge Base

Modify a hidden parameter in Oracle Database

Read, change in the SPFILE and verify an Oracle hidden parameter, with listing queries.

A hidden parameter (a name starting with an underscore) is changed with ALTER SYSTEM, just like a regular parameter. This article summarises the procedure from a post on stepintooracledba. Hidden parameters are undocumented: check Oracle's recommendation before changing a value.

1. Check the current value

Select name,VALUE,ISDEFAULT,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name like '%real%';

Example output:

NAME                                VALUE                     ISDEFAULT ISSES ISSYS_MOD
----------------------------------- ------------------------- --------- ----- ---------
_realfree_heap_pagesize_hint        65536                     FALSE     FALSE FALSE

2. Change the parameter in the SPFILE

alter system set "_realfree_heap_pagesize_hint"=262144 scope=spfile sid='*';
Point Detail
RAC environment Use sid='*' to apply the value to all instances.
Quotes The name of a hidden parameter must be enclosed in double quotes. This is not needed for a regular parameter.

3. Restart the database

Example on a RAC cluster with srvctl (placeholder database and node names):

srvctl stop database -d orcl_db
srvctl start database -d orcl_db -n node01
srvctl status database -d orcl_db

4. Check the new value

Run the query from step 1 again. The VALUE column should show 262144.

List hidden parameters

List all hidden parameters with their session and instance values:

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;

List the desupported parameters:

SELECT kspponm NAME,
       DECODE(ksppoflg,1,'DESUPPORTED','UNDERSCORE') SETTING
FROM   x$ksppo
WHERE ksppoflg = 1
ORDER BY NAME ASC;

Set, reset and review

Action Command
Set a hidden parameter ALTER SYSTEM SET "_log_io_size"= 1048576 SCOPE = SPFILE;
Reset to the default ALTER SYSTEM RESET db_block_buffers SCOPE=SPFILE SID='*';
Show the hidden parameters that are set SELECT * FROM v$parameter WHERE SUBSTR(NAME, 0,1) ='_';

Notes

Mastodon LLIMastodon SocialFosstodon