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 Niall Litchfield

Reply: 1 2 3 4 5

you might also think about whether any errors that will cause the trigger to
fire might be triggered if the emailing doesn't work. That would be rather
unfortunate....

On 7/28/06, Niall Litchfield <niall.litchfield@(protected)> wrote:
>
> 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@(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
> >
>
>
>
> --
> Niall Litchfield
> Oracle DBA
> http://www.orawin.info
>

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

you might also think about whether any errors that will cause the trigger to
fire might be triggered if the emailing doesn't work. That would be rather
unfortunate.... 
<div>On 7/28/06, Niall
Litchfield <<a href="mailto:niall.litchfield@(protected)">niall.litchfield
@(protected)</a>> wrote:
<blockquote class="gmail_quote" style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0
.8ex; BORDER-LEFT: #ccc 1px solid">
<div>
<div>errrm the 9291 error surely should only be a temporary error and not one
regular enough to email about :) </div>
<div> </div>
<div>for errors that don't have a user or termin al then don't try and capture
them.  </div>
<div></div>
<div>On 7/28/06, Wojciech Skrzynecki <<a onclick="return top.js.OpenExtLink
(window,event,this)" href="mailto:wojciech.skrzynecki@(protected)" target="_blank"
>
wojciech.skrzynecki@(protected)</a>> wrote: </div>
<div>
<blockquote class="gmail_quote" style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0
.8ex; BORDER-LEFT: #ccc 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 &nbsp
;           varchar2(100) := ' test';
   d_mailhost 
  VARCHAR2(30) := 'test';
   d_mail_conn            &nbsp
;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>
<div></div></div>
<div> -- Niall Litchfield Oracle
DBA <a onclick="return top.js.OpenExtLink(window,event,this)" href="http:/
/www.orawin.info/" target="_blank">http://www.orawin.info </a>
</div></blockquote></div> -- Niall Litchfield Oracle DBA <a href="http://www.orawin.info">http://www.orawin.info</a>