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 Wojciech Skrzynecki

Reply: 1 2 3 4 5

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

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

-- Wojciech Skrzynecki Database Administrator