Jeffrey,
writing to trace aka alert log is pretty easy, there are at least 2 ways I
know of:
- the (well not really) undocumented one
begin
sys.dbms_system.ksdwrt(2, 'HEEEEEEEEEEEEEEEEEEEEEEEEEELP');
end;
KSDWRT(iiWhat, ivMsg) writes a message to the alert file. The first
parameter iiWhat takes input as
1 (write to trace file),
2 (write to alert log) or
3 (write to both trace and alert log files).
The second parameter ivMsg is the actual text message that should go into
the file(s).
- an utl_file based solution
create or replace procedure write_alert(iv_msg varchar2)
as
l_alert_dir varchar2(256);
l_sql varchar2(1024);
l_dbname varchar2(32);
l_fnam varchar2(32);
begin
select
name into l_alert_dir
from
v$parameter
where
name = 'background_dump_destination';
-- set directory for utl_file package
-- bounce instance when on 8i
l_sql := 'alter system set utl_file_dir = ' || l_alert_dir;
execute immediate l_sql;
-- now fiddle with the actual filename
select name into l_dbname from v$database;
-- dbms_output.put_line(l_dbname);
l_fnam := 'alert_'||l_dbname||'.log';
utl_file.fopen(l_alert_dir,l_fnam,'W');
dbms_output.put_line(iv_msg);
utl_file.fclose(':alert_loc');
end;
And last but not least you might find the pollowing package helpful, as it
allows you to extract information from the ALERT LOG, thus giving you a
solid basis for troubleshooting, etc.
Sorry, comments in German language, but I don't have the time to change that
now :-)
create or replace package alert as
procedure log ;
procedure truncate ;
end alert;
create or replace package body alert as
procedure log is
/* fuer File i/o mittels UTL_FILE (ab 7.3)
/* --------------------------------------- */
TYPE file_type IS RECORD (id BINARY_INTEGER);
myftyp utl_file.file_type;
loc varchar2(80);
fnam varchar2(80);
omode varchar2(10):= 'r';
lines natural := 1;
/* --------------------------------------- */
anz number := 0;
maxline# number := 0;
maxline1# number := 0;
line varchar2(1000);
c number := 0;
dbname v$database.name%type;
zeit varchar2(20);
begin
dbms_output.disable;
dbms_output.enable(1000000);
-- dbms_output.put_line('jetzt gehts los!');
select value into loc from v$parameter where name='background_dump_dest';
-- dbms_output.put_line(loc);
select name into dbname from v$database;
-- dbms_output.put_line(dbname);
if substr(dbname,1,5) = 'C2000' then
fnam := 'alert_'||'c2000'||'.log';
else
fnam := 'alert_'||dbname||'.log';
end if;
-- dbms_output.put_line(fnam);
myftyp := utl_file.fopen(loc,fnam,omode);
--
-- maxline1#: Zeilennummer des letzten ORA-
--
select count(*) into c from alertlog where service!='Check';
-- if c > 0 then
-- dbms_output.put_line(' delete ');
-- delete from alertlog where service!='Check';
-- commit;
-- end if;
if c > 0 then
select max(lastline#) into maxline1# from alertlog
where service != 'Check';
end if;
--
-- maxline#: Zeilennummer des letzten Checks
--
select count(*) into c from alertlog where service = 'Check';
if c > 0 then
select max(lastline#) into maxline# from alertlog
where service = 'Check';
end if;
<<loop_read>>
loop
utl_file.get_line(myftyp,line);
anz := anz + 1;
if (substr(line,1,4) = 'ORA-') AND (anz > maxline1#) then
insert into alertlog values(dbname,anz,line);
commit;
end if;
end loop loop_read;
<<ende>>
-- dbms_output.put_line('anz: '||anz);
-- dbms_output.put_line('das ist das Ende!');
if utl_file.is_open(myftyp) then utl_file.fclose(myftyp);
end if;
exception
-- fclose
when utl_file.invalid_path then
dbms_output.put_line('invalid_path');
if utl_file.is_open(myftyp) then utl_file.fclose(myftyp);
end if;
-- fclose
when utl_file.invalid_mode then
dbms_output.put_line('invalid_mode');
if utl_file.is_open(myftyp) then utl_file.fclose(myftyp);
end if;
-- fclose
-- get_line
when utl_file.invalid_operation then
dbms_output.put_line('invalid_operation');
if utl_file.is_open(myftyp) then utl_file.fclose(myftyp);
end if;
-- get_line
when utl_file.invalid_filehandle then
dbms_output.put_line('invalid_filehandle');
if utl_file.is_open(myftyp) then utl_file.fclose(myftyp);
end if;
-- get_line
when utl_file.read_error then
dbms_output.put_line('read_error');
if utl_file.is_open(myftyp) then utl_file.fclose(myftyp);
end if;
-- get_line
when no_data_found then
dbms_output.put_line('no_data_found => fertig!!!');
if anz < maxline# then
alert.truncate;
end if;
select to_char(sysdate,'dd.mm.yyyy hh24:mi:ss') into zeit from
dual;
insert into alertlog values('Check',anz,'am: '||zeit);
commit;
if utl_file.is_open(myftyp) then utl_file.fclose(myftyp);
end if;
-- get_line
when value_error then
dbms_output.put_line('value_error');
if utl_file.is_open(myftyp) then utl_file.fclose(myftyp);
end if;
when others then
dbms_output.put_line('SQLCODE: '||sqlcode);
dbms_output.put_line('SQLERRM(SQLCODE): '||sqlerrm(sqlcode));
if utl_file.is_open(myftyp) then utl_file.fclose(myftyp);
end if;
end log;
procedure truncate is
cid integer;
begin
cid := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cid,'TRUNCATE TABLE ALERTLOG',dbms_sql.v7);
DBMS_SQL.CLOSE_CURSOR(cid);
end truncate;
end alert;
hth,
gerald
-----Ursprüngliche Nachricht-----
Von: Jeffrey Beckstrom
[mailto:oracledba-ezmlmshield-x11020292.[Email address protected]
Gesendet: Thursday, October 28, 2004 6:26 PM
An: LazyDBA Discussion
Betreff: write to alert log
In the back of my mind I recall that it is possible to write to the
alert.log from procedure, but can not recall how. What do I execute to
do this?
Jeffrey Beckstrom
Database Administrator
Greater Cleveland Regional Transit Authority
1240 W. 6th Street
Cleveland, Ohio 44113
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
Oracle LazyDBA home page