Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: after servererror trigger

Re: after servererror trigger

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Fri, 28 Jul 2006 16:59:19 +0100
Message-ID: <7765c8970607280859i3fde7dbeyc9a653c5316418a0@mail.gmail.com>


errrm the 9291 error surely should only be a temporary error and not one regular enough to email about :)

for errors that don't have a user or termin al then don't try and capture them.

On 7/28/06, Wojciech Skrzynecki <wojciech.skrzynecki_at_gmail.com> wrote:
>
> Hello
>
>
>
> I would like to ask you about after servererror trigger. I wrote trigger
> as below:
>
>
>
> CREATE OR REPLACE TRIGGER notification_error
>
> after servererror on database
>
> declare
>
> d_sender varchar2(30) := ora_database_name;
>
> d_user varchar2(30) :=ora_login_user;
>
> d_rcp varchar2(100) := ' test';
>
> d_mailhost VARCHAR2(30) := 'test';
>
> d_mail_conn utl_smtp.connection;
>
> d_terminal VARCHAR2(30) :=userenv('terminal');
>
> d_current_nr_error number := ora_server_error(1);
>
> ora_server_error_msg varchar2(100);
>
> BEGIN
>
> if d_current_nr_error between 01800 and 02231
>
> or d_current_nr_error in (09291,16014)
>
> then
>
> ora_server_error_msg := SQLERRM(-d_current_nr_error);
>
> d_mail_conn := utl_smtp.open_connection(d_mailhost);
>
> utl_smtp.helo(d_mail_conn, d_mailhost );
>
> utl_smtp.mail(d_mail_conn, d_sender);
>
> utl_smtp.rcpt(d_mail_conn, d_rcp);
>
> utl_smtp.open_data(d_mail_conn);
>
> utl_smtp.write_data(d_mail_conn, utl_tcp.CRLF || 'From: ' ||
> ora_database_name);
>
> utl_smtp.write_data(d_mail_conn, utl_tcp.CRLF || 'Subject: ' ||
> ora_server_error_msg);
>
> utl_smtp.write_data(d_mail_conn, utl_tcp.CRLF || 'User: ' ||
> ora_login_user);
>
> utl_smtp.write_data(d_mail_conn, utl_tcp.CRLF || 'Terminal: ' ||
> d_terminal);
>
> utl_smtp.write_data(d_mail_conn, utl_tcp.CRLF || 'Date: ' ||
> to_char(sysdate, 'RRRR-MM-RR HH24:MI:SS') );
>
> utl_smtp.close_data(d_mail_conn);
>
> utl_smtp.quit(d_mail_conn);
>
> end if;
>
>
>
> I do not know how to intercept background ORA error example "ORA-01652:
> unable to extend temp segment by 512 in tablespace TEMP" or
>
> "ORA-9291 invalid device specified for archive destination "
>
>
>
> Could you explain me how to do this?
>
>
>
> Thanks for help
> --
>
> Wojciech Skrzynecki
> Database Administrator
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Jul 28 2006 - 10:59:19 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US