check_parameter.sql
Download SQL script
check_parameter.sql
-- -----------------------------------------------------------------------------
-- WWW.PETEFINNIGAN.COM LIMITED
-- -----------------------------------------------------------------------------
-- Script Name : check_parameter.sql
-- Author : Pete Finnigan
-- Date : June 2004
-- -----------------------------------------------------------------------------
-- Description : Use this script to find the value of an Oracle initialisation
-- parameter. It handles undocument ("_" underscore) parameters
-- as well as normal ones. You can also pass in the value that the
-- parameter should have and the procedure indicates if the setting
-- violates this or not.
--
-- The output can be directed to either the screen via dbms_output
-- or to a file via utl_file. The method is decided at run time
-- by choosing either 'S' for screen or 'F' for File. If File is
-- chosen then a filename and output directory are needed. The
-- output directory needs to be enabled via utl_file_dir prior to
-- 9iR2 and a directory object after.
-- -----------------------------------------------------------------------------
-- Maintainer : Pete Finnigan (www.petefinnigan.com)
-- Copyright : Copyright (C) 2004 PeteFinnigan.com Limited. All rights
-- reserved. All registered trademarks are the property of their
-- respective owners and are hereby acknowledged.
-- -----------------------------------------------------------------------------
-- Usage : The script provided here is available free. You can do anything
-- you want with it commercial or non commercial as long as the
-- copyrights and this notice are not removed or edited in any way.
-- The scripts cannot be posted / published / hosted or whatever
-- anywhere else except at www.petefinnigan.com/tools.htm
-- -----------------------------------------------------------------------------
-- Version History
-- ===============
--
-- Who version Date Description
-- === ======= ====== ======================
-- P.Finnigan 1.0 Jun 2004 First Issue.
-- P.Finnigan 1.1 Apr 2005 Added whenever sqlerror continue to stop
-- subsequent errors barfing SQL*Plus. Thanks
-- to Norman Dunbar for the update.
-- P.Finnigan 1.2 Apr 2005 Corrected spelling mistake in the output.
-- -----------------------------------------------------------------------------
whenever sqlerror exit rollback
set feed on
set head on
set arraysize 1
set space 1
set verify off
set pages 25
set lines 80
set termout on
clear screen
set serveroutput on size 1000000
spool check_parameter.lis
undefine param_to_check
undefine param_val
undefine output_method
undefine file_name
undefine output_dir
set feed off
col system_date noprint new_value val_system_date
select to_char(sysdate,'Dy Mon dd hh24:mi:ss yyyy') system_date from sys.dual;
set feed on
prompt check_parameter: Release 1.0.2.0.0 - Production on &val_system_date
prompt Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.
prompt
accept param_to_check char prompt 'PARAMETER TO CHECK [utl_file_dir]: ' default utl_file_dir
accept param_val char prompt 'CORRECT VALUE [null]: ' default null
accept output_method char prompt 'OUTPUT METHOD Screen/File [S]: ' default S
accept file_name char prompt 'FILE NAME FOR OUTPUT [priv.lst]: ' default priv.lst
accept output_dir char prompt 'OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]: ' default /tmp
prompt
declare
--
lg_fptr utl_file.file_type;
lv_file_or_screen varchar2(1):='S';
--
procedure open_file (pv_file_name in varchar2,
pv_dir_name in varchar2) is
begin
lg_fptr:=utl_file.fopen(pv_dir_name,pv_file_name,'A');
exception
when utl_file.invalid_path then
dbms_output.put_line('invalid path');
when utl_file.invalid_mode then
dbms_output.put_line('invalid mode');
when utl_file.invalid_filehandle then
dbms_output.put_line('invalid filehandle');
when utl_file.invalid_operation then
dbms_output.put_line('invalid operation');
when utl_file.read_error then
dbms_output.put_line('read error');
when utl_file.write_error then
dbms_output.put_line('write error');
when utl_file.internal_error then
dbms_output.put_line('internal error');
when others then
dbms_output.put_line('ERROR (open_file) => '||sqlcode);
dbms_output.put_line('MSG (open_file) => '||sqlerrm);
end open_file;
--
procedure close_file is
begin
utl_file.fclose(lg_fptr);
exception
when utl_file.invalid_path then
dbms_output.put_line('invalid path');
when utl_file.invalid_mode then
dbms_output.put_line('invalid mode');
when utl_file.invalid_filehandle then
dbms_output.put_line('invalid filehandle');
when utl_file.invalid_operation then
dbms_output.put_line('invalid operation');
when utl_file.read_error then
dbms_output.put_line('read error');
when utl_file.write_error then
dbms_output.put_line('write error');
when utl_file.internal_error then
dbms_output.put_line('internal error');
when others then
dbms_output.put_line('ERROR (close_file) => '||sqlcode);
dbms_output.put_line('MSG (close_file) => '||sqlerrm);
end close_file;
--
procedure write_op (pv_str in varchar2) is
begin
if lv_file_or_screen='S' then
dbms_output.put_line(pv_str);
else
utl_file.put_line(lg_fptr,pv_str);
end if;
exception
when utl_file.invalid_path then
dbms_output.put_line('invalid path');
when utl_file.invalid_mode then
dbms_output.put_line('invalid mode');
when utl_file.invalid_filehandle then
dbms_output.put_line('invalid filehandle');
when utl_file.invalid_operation then
dbms_output.put_line('invalid operation');
when utl_file.read_error then
dbms_output.put_line('read error');
when utl_file.write_error then
dbms_output.put_line('write error');
when utl_file.internal_error then
dbms_output.put_line('internal error');
when others then
dbms_output.put_line('ERROR (write_op) => '||sqlcode);
dbms_output.put_line('MSG (write_op) => '||sqlerrm);
end write_op;
--
procedure get_param (pv_param in varchar2, pv_val in varchar2) is
--
cursor c_main (cp_param in varchar2) is
select x.ksppinm name,
y.ksppstvl value,
decode(ksppity,1,'BOOLEAN',2,'STRING',3,'INTEGER',
4,'PARAMETER FILE',5,'RESERVED',6,'BIG INTEGER','UNKNOWN') typ,
decode(ksppstdf,'TRUE','DEFAULT VALUE','FALSE','***SPECIFIED IN INIT.ORA') isdefault,
decode(bitand(ksppiflg/256,1),1,'***CAN BE CHANGED WITH ALTER SESSION','FALSE') isses_modifiable,
decode(bitand(ksppiflg/65536,3),1,'CAN BE CHANGED IMMEDIATELY WITH ALTER SYSTEM',
2,'***CAN BE CHANGED WITH ALTER SYSTEM, CHANGES TAKE EFFECT IN SUBSEQUENT SESSIONS',
3,'***CAN BE CHANGED IMMEDIATELY WITH ALTER SYSTEM','FALSE') issys_modifiable,
decode(bitand(ksppstvf,7),1,'WAS MODIFIED WITH ALTER SESSION',
4,'***WAS MODIFIED WITH ALTER SYSTEM','FALSE') is_modified,
decode(bitand(ksppstvf,2),2,'***ORACLE CHANGED THE VALUE ON STARTUP','FALSE') is_adjusted,
ksppdesc description,
ksppstcmnt update_comment
from x$ksppi x,
x$ksppcv y
where x.inst_id = userenv('Instance')
and y.inst_id = userenv('Instance')
and x.indx = y.indx
and x.ksppinm = cp_param;
lv_main c_main%rowtype;
--
begin
open c_main(pv_param);
fetch c_main into lv_main;
if c_main%found then
write_op('Name : '||lv_main.name);
write_op('Value : '||lv_main.value);
write_op('Type : '||lv_main.typ);
write_op('Is Default : '||lv_main.isdefault);
write_op('Is Session modifiable : '||lv_main.isses_modifiable);
write_op('Is System modifiable : '||lv_main.issys_modifiable);
write_op('Is Modified : '||lv_main.is_modified);
write_op('Is Adjusted : '||lv_main.is_adjusted);
write_op('Description : '||lv_main.description);
write_op('Update Comment : '||lv_main.update_comment);
write_op('-------------------------------------------------------------------------');
if to_char(lv_main.value) <> nvl(pv_val,'NULL') then
write_op('value ***'||lv_main.value||'*** is incorrect');
else
write_op('value is correct');
end if;
else
write_op('ERROR: PARAMETER '||pv_param||' NOT FOUND');
end if;
exception
when others then
dbms_output.put_line('ERROR (get_param) => '||sqlcode);
dbms_output.put_line('MSG (get_param) => '||sqlerrm);
end get_param;
begin
lv_file_or_screen:= upper('&&output_method');
if lv_file_or_screen='F' then
open_file('&&file_name','&&output_dir');
end if;
write_op('Investigating parameter => '||'&¶m_to_check');
write_op('====================================================================');
get_param('&¶m_to_check','&¶m_val');
if lv_file_or_screen='F' then
close_file;
end if;
exception
when others then
dbms_output.put_line('ERROR (main) => '||sqlcode);
dbms_output.put_line('MSG (main) => '||sqlerrm);
end;
/
prompt For updates please visit www.petefinnigan.com/tools.htm
prompt
spool off
undefine param_to_check
undefine param_val
undefine output_method
undefine file_name
undefine output_dir
whenever sqlerror continue
