How to change Oracle hidden parameters

Thank you for visiting Spatial DBA - Oracle and ArcSDE.

Please visit Oracle DBA Tips (http://www.oracledbatips.com) for more Oracle DBA Tips.
==================================================================
 

Oracle hidden parameters start with "_". They can not be viewed from the output of show parameter,
or by querying v$parameter unless and until they are set explicitly in init.ora.

If you want to view all the hidden parameters and their default values, the following query
could be of help,

SELECT a.ksppinm "Parameter", b.ksppstvl "Session Value", c.ksppstvl "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 '/';

To see the listing of all hidden parameters query,

select * from SYS.X$KSPPI where substr(KSPPINM,1,1) = '_';

It is never recommended to modify Oracle hidden parameters without the assistance of Oracle Support. Changing these parameters may lead to high performance degradation and other problems in the database.

Methods to change Oracle hidden parameters:

1) If you use pfile, you can entry of the hidden parameter into initSID.ora, and restart the instance.

"_shared_pool_reserved_pct"=10

2) If you want to set for the current session, use ALTER SESSION SET ...

3) If you want to set it permanently and you are using spfile, use ALTER SYSTEM SET ...... SCOPE=BOTH

Some hidden parameters cannot be changed in MEMORY, such as _shared_pool_reserved_pct. See below:

SQL> show parameter pfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string /fs/u01/oracle/product/1 0.2.0.2ee/dbs/spfilemydb.ora SQL> alter system set "_shared_pool_reserved_pct"=10 scope=both; alter system set "_shared_pool_reserved_pct"=10 scope=both * ERROR at line 1: ORA-02095: specified initialization parameter cannot be modified SQL> alter system set "_shared_pool_reserved_pct"=10 scope=memory; alter system set "_shared_pool_reserved_pct"=10 scope=memory * ERROR at line 1: ORA-02095: specified initialization parameter cannot be modified SQL> alter system set "_shared_pool_reserved_pct"=10 scope=spfile; System altered.

If SCOPE=SPFILE, the instance needs to be restarted.