We took a trigger from Nico Booyse made some minor changes and with some shell scripts we put out an error report on all our production databases daily (see below trigger) It was really to ascertain if we really got errors on the DBs people said they did, so we wouldn't be looking for problems in the wrong DB. We wanted to get the text for some of the messages like 900 etc. since we were seeing 1000s of some of these occurring. I got the answer from Connor McDonald's latest book and one of the other DBAs (Rose Fidanzo) coded it up. It works but she found METALINK saying there was a Bug No. 3124081 that could bring the database down. Their workaround was to not accept the ones that caused a problem. Instead, we elected to have an IN list for what we were interested in. But I'm really nervous about putting new version in production systems. Especially as we're supposed to save them money not crashing their DBs. So I was wondering if we're pretty safe in using the IN list or should we just forget about it till the (Fixed in Product Version 10.0) patch? Thanks Larry W. Also Bug No. 3264549 SET ECHO ON SET TIMING ON rem ----------------------------------------------------------------------- rem Filename: db-error.sql rem Purpose: Log all database errors to a table rem Oracle8i or above/ DBA or CREATE ANY TRIGGER privs/ and remGRANT SELECT ON SYS.V_$SESSION required rem Date: 21-Mar-2000 rem Author: Nico Booyse (booysen@xxxxxxxxxxx) rem ----------------------------------------------------------------------- drop trigger log_errors_trig; drop table log_errors_tab; create table log_errors_tab (error varchar2(30) ,timestamp date ,username varchar2(30) ,osuser varchar2(30) ,machine varchar2(64) ,process varchar2(09) ,program varchar2(48) ,sqltext varchar2(1000) ) TABLESPACE TOOLS STORAGE (INITIAL 1M NEXT 1M MAXEXTENTS 9999 PCTINCREASE 0) ; create or replace trigger log_errors_trig after servererror on database declare var_user varchar2(30); var_osuser varchar2(30); var_machine varchar2(64); var_process varchar2(09); var_program varchar2(48); var_sqltext varchar2(1000); sql_text ora_name_list_t; n number; begin select s.username, s.osuser, s.machine, s.process, s.program into var_user, var_osuser, var_machine, var_process, var_program from sys.v_$session s ,sys.v_$sqlarea t where s.audsid = userenv('sessionid') and s.prev_sql_addr = t.address(+) and s.prev_hash_value = t.hash_value(+) ; if dbms_standard.server_error(1) in (900,942) then n:= ora_sql_txt(sql_text); for i in 1..n loop var_sqltext:=var_sqltext||sql_text(i); end loop; end if; insert into log_errors_tab values(dbms_standard.server_error(1),sysdate,var_user, var_osuser,var_machine,var_process,var_program,var_sqltext); end; / SHOW ERRORS SELECT * FROM Oracle_support; SELECT * FROM log_errors_tab; Created at 21-APR-2004 14 00 01 for {DATABASE NAME} ERROR COUNT(*) OSUSER USERNAME ----------------------------- --------- ------------ ------------ 1 291 frmwk EDRPUSER 1 3 openqaar EDRPUSER 1 153 servclus EDRPUSER 1 2 vsweb VSWEB 1017 1 IKaliapp 1400 6 frmwk EDRPUSER 1401 2 frmwk EDRPUSER 1722 3 XXXXXXXX EDRPUSER 1795 6 servclus EDRPUSER 20101 98 frmwk EDRPUSER 20101 19 servclus EDRPUSER 2291 122 frmwk EDRPUSER 22920 2 vsweb VSWEB 600 1 servclus EDRPUSER 900 7612 servclus EDRPUSER 904 9 XXXXXXXX EDRPUSER 918 1 XXXXXXXX EDRPUSER 920 5 107599 EDRPUSER 933 3 XXXXXXXX EDRPUSER 936 1 XXXXXXXX EDRPUSER 942 12 dsadm EDMREAD 942 371 dsadm VANTUSER 32 rows selected. 00001, 00000, "unique constraint (%s.%s) violated" // *Cause: An UPDATE or INSERT statement attempted to insert a duplicate key. // For Trusted Oracle configured in DBMS MAC mode, you may see // this message if a duplicate entry exists at a different level. // *Action: Either remove the unique restriction or do not insert the key. 01017, 00000, "invalid username/password; logon denied" // *Cause: // *Action: 01400, 00000, "cannot insert NULL into (%s)" // *Cause: // *Action: 01401, 00000, "inserted value too large for column" // *Cause: // *Action: 01722, 00000, "invalid number" // *Cause: // *Action: 01795, 00000, "maximum number of expressions in a list is 1000" // *Cause: // *Action: 02291, 00000,"integrity constraint (%s.%s) violated - parent key not found" // *Cause: A foreign key value has no matching primary key value. // *Action: Delete the foreign key or add a matching primary key. 22920, 00000, "row containing the LOB value is not locked" // *Cause: The row containing the LOB value must be locked before // updating the LOB value. // *Action: Lock the row containing the LOB value before updating the LOB // value. 00600, 00000, "internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]" // *Cause: This is the generic internal error number for Oracle program // exceptions. This indicates that a process has encountered an // exceptional condition. // *Action: Report as a bug - the first argument is the internal error number 00900, 00000, "invalid SQL statement" // *Cause: // *Action: 00904, 00000, "invalid column name" // *Cause: // *Action: 00918, 00000, "column ambiguously defined" // *Cause: // *Action: 00920, 00000, "invalid relational operator" // *Cause: // *Action: 00923, 00000, "FROM keyword not found where expected" // *Cause: // *Action: 00933, 00000, "SQL command not properly ended" // *Cause: // *Action: 00936, 00000, "missing expression" // *Cause: // *Action: 00942, 00000, "table or view does not exist" // *Cause: // *Action: ********************************************************************** The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. Thank You. ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------