How-to show oracle parameters including hiddened ones

Save below SQL to a file named p.sql,

set pagesize 100
set linesize 150
col name format A45
col value format A20
col description format A70

select ksppinm name, ksppstvl value, ksppdesc description
from x$ksppi x, x$ksppcv y
where (x.indx = y.indx)
and ( ksppinm like '%&1%' or ksppdesc like '%&1%' )
order by name;

Run it and get this kind of output:

SYS@DB: SQL> @p undo_reten
old   4: and ( ksppinm like '%&1%' or ksppdesc like '%&1%' )
new   4: and ( ksppinm like '%undo_reten%' or ksppdesc like '%undo_reten%' )

NAME                                          VALUE                DESCRIPTION
--------------------------------------------- -------------------- ----------------------------------------------------------------------
_undo_autotune                                TRUE                 enable auto tuning of undo_retention
undo_retention                                3000                 undo retention in seconds

Advertisements

About Alex Zeng
I would be very happy if this blog can help you. I appreciate every honest comments. Please forgive me if I'm too busy to reply your comments in time.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: