Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> after servererror trigger
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 -- http://www.freelists.org/webpage/oracle-lReceived on Fri Jul 28 2006 - 09:45:25 CDT
![]() |
![]() |