Thursday, October 9, 2014

How to find hidden parameters in the database

set lines 200
col description format a70
col value format a20
col name format a30
SELECT name,value,description from SYS.V$PARAMETER WHERE name  LIKE '\_%' ESCAPE '\';
SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2  WHERE  UPPER(name) ='EVENT' AND  isdefault='FALSE';
SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2 WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE';

If desirable, you can generate a "reset" script, which preserves your hidden parameters, like this:
set lines 200
set pages 0
set feedback off
set verify off
set echo off
set heading off
set trimspool on
spool original_hidden_params.sql
select '-- original hidden parameter values' from dual;
select 'alter system set "' || name || '"=' || value || ' scope=spfile;' from SYS.V$PARAMETER WHERE name  LIKE '\_%' ESCAPE '\';
select 'exit' from dual;
spool off
spool reset_hidden_params.sql
select '-- reset hidden parameter' from dual;
select 'alter system reset "' || name || '" scope=spfile;' from SYS.V$PARAMETER WHERE name  LIKE '\_%' ESCAPE '\';
select 'exit' from dual;
exit

To set a hidden parameter in Your instance, see this post.

No comments:

Post a Comment