RE: after servererror trigger

You can use ora_space_error_info to determine if your server error is 
space-related (such as ora-01652).  
 

Paul Baumgartel 
CREDIT SUISSE 
Information Technology 
DBA & Admin - NY, KIGA 1 
11 Madison Avenue 
New York, NY 10010 
USA 
Phone 212.538.1143 
paul.baumgartel@xxxxxxxxxxxxxxxxx 
www.credit-suisse.com 

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On 
Behalf Of goran bogdanovic
Sent: Friday, July 28, 2006 11:08 AM
To: wojciech.skrzynecki@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: after servererror trigger


write a script to scan the ora trace/log files for errors? - and get rid of 
trigger...:-)


On 7/28/06, Wojciech Skrzynecki <  <mailto:wojciech.skrzynecki@xxxxxxxxx> 
wojciech.skrzynecki@xxxxxxxxx> 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 



==============================================================================
Please access the attached hyperlink for an important electronic communications 
disclaimer: 

http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
==============================================================================

Other related posts: