Occasionally I need to view hidden parameters in Oracle.

There are plenty of queries and scripts that already do this out there. But these queries have to be run as SYS. So when I need to view hidden parameters, I often find myself having to use SQL*Plus to do this.

Running queries on SQL*Plus is a bit of an ordeal, because then you have to worry about formatting the results of your query to fit your console window.

Here is a query that formats everything to fit nicely in my full-screen PuTTy window in SQL*Plus:

set linesize 400
set pagesize 10000
col parameter_name for a64
col parameter_desc for a90
col parameter_value for a64

select pi.ksppinm parameter_name
     , pi.ksppdesc parameter_desc
     , cv.ksppstvl parameter_value
from sys.x$ksppi pi
   , sys.x$ksppcv cv
where pi.indx = cv.indx
order by trim(leading '_' from pi.ksppinm)

Leave a Reply

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