Tuesday, April 5, 2016

Oracle- Init Parameters Queries

Oracle- Init Parameters Queries

Show latent parameter changes

select name, value from v$parameter where ismodified != 'FALSE';

Show non-default parameters

set pages 999 lines 100
col name format a30
col value format a50
select name, value from v$parameter where isdefault = 'FALSE' and value is not nullorder by name;

or

set pages 999 lines 100
select name || '=' || decode(type, 2, '''') || value || decode(type, 2, '''') parameter from v$parameter where isdefault = 'FALSE' and value is not null order by name;


Reset/Unset a spfile parameter

Setting a parameter to =' ' often isn't enough. Do this instead...
alter system reset <parameter> scope=spfile sid='*';

The sid='*' bit is always necessary, even in non RAC database.

No comments:

Post a Comment