after servererror trigger

Links

Frequent Oracle Errors

•	TNS:could not resolve the connect identifier specified
•	Backtrace message unwound by exceptions
•	invalid identifier
•	PL/SQL compilation error
•	internal error
•	missing expression
•	table or view does not exist
•	end-of-file on communication channel
•	TNS:listener unknown in connect descriptor
•	insufficient privileges
•	PL/SQL: numeric or value error string
•	TNS:protocol adapter error
•	ORACLE not available
•	target host or object does not exist
•	invalid number
•	unable to allocate string bytes of shared memory
•	resource busy and acquire with NOWAIT specified
•	error occurred at recursive SQL level string
•	ORACLE initialization or shutdown in progress
•	archiver error. Connect internal only, until freed
•	snapshot too old
•	unable to extend temp segment by string in tablespace
•	Credential retrieval failed
•	missing or invalid option
•	invalid username/password; logon denied
•	unable to create INITIAL extent for segment
•	out of process memory when trying to allocate string bytes
•	shared memory realm does not exist
•	cannot insert NULL
•	TNS:unable to connect to destination
•	remote database not found'>ora-02019
•	exception encountered: core dump
•	inconsistent datatypes
•	no data found
•	TNS:operation timed out
•	PL/SQL: could not find program
•	existing state of packages has been discarded
•	maximum number of processes exceeded
•	error signaled in parallel query server
•	ORACLE instance terminated. Disconnection forced
•	TNS:packet writer failure
•	see ORA-12699
•	missing right parenthesis
•	name is already used by an existing object
•	cannot identify/lock data file
•	invalid file operation
•	quoted string not properly terminated

after servererror trigger

after servererror trigger

2006-07-28 - By Baumgartel, Paul

Reply: 1 2 3 4 5

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

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@(protected)
www.credit-suisse.com

-- --Original Message-- --
From: oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)]On
Behalf Of goran bogdanovic
Sent: Friday, July 28, 2006 11:08 AM
To: wojciech.skrzynecki@(protected)
Cc: oracle-l@(protected)
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@(protected)>
wojciech.skrzynecki@(protected)> 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 (See ORA-01652.ora-code.com): unable
to extend temp segment by 512 in tablespace TEMP" or

"ORA-9291 (See ORA-9291.ora-code.com) 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
==============================================================================

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">

<META content="MSHTML 6.00.2800.1556" name=GENERATOR></HEAD>
<BODY>
<DIV>You
can use ora_space_error_info to determine if your server error is space-related
(such as ora-01652 (See ora-01652.ora-code.com)).  </DIV>
<DIV> </DIV>
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@(protected) www.credit-suisse.com 
<BLOCKQUOTE>
<DIV class=OutlookMessageHeader dir=ltr align=left>-- --Original Message-- -- From:
oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)]On
Behalf Of goran bogdanovic Sent: Friday, July 28, 2006 11:08
AM To: wojciech.skrzynecki@(protected) Cc:
oracle-l@(protected) Subject: Re: after servererror
trigger </DIV>write a script to scan the ora trace/log files
for
errors? - and get rid of trigger...:-) 
<DIV>On 7/28/06, Wojciech
Skrzynecki <<A href="mailto:wojciech.skrzynecki@(protected)">
wojciech.skrzynecki@(protected)</A>> wrote:
<BLOCKQUOTE class=gmail_quote
style="PADDING-LEFT: 1ex; MARGIN: 0pt 0pt 0pt 0.8ex; BORDER-LEFT: rgb(204,204
,204) 1px solid">
<DIV>
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 (See ORA-01652.ora-code.com): unable to extend temp segment by
512 in tablespace TEMP" or <PRE>"ORA-9291 (See ORA-9291.ora-code.com) invalid device specified for archive destination
"</PRE><PRE> </PRE>
Could you explain me how to do
this?
 
Thanks for help-- </DIV>
<DIV>Wojciech Skrzynecki Database Administrator
</DIV></BLOCKQUOTE></DIV> </BLOCKQUOTE>
<pre wrap>==================================================================
============
Please access the attached hyperlink for an important electronic communications
disclaimer:

http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
==============================================================================
</pre></BODY></HTML>