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
- The number of hidden parameters depends on the version: the listing query returned 603 parameters on Oracle 9i, and the desupported query returned 40.
- Double quotes are required for hidden parameter names, and
sid='*'only matters in RAC environments.