:

Oracle Hidden Parameters

Under normal circumstances you shouldn’t have any need to modify the Oracle hidden parameters, which all start with the underscore character. This is probably why you can’t see them very easily in the database with the normal SHOW command.

If you would like to find out what hidden Oracle parameters are set within your database, you can run the query below:

SELECT
a.ksppinm “Parameter”,
a.ksppdesc “Description”,
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 ‘/’;

The query above will display all of the hidden parameters within your Oracle database. These parameters will not be visible when using the “show <parameter name>” command or when querying v$parameter or v$spparameter.

Please take care when setting any hidden parameter. The only reasons to set them is at the advice of Oracle support. However, I would recommend having a play around with them on a DEV environment which is OK to be trashed! Have fun…

Like it, share it...

Category: Database Monitoring


Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *