RE: Error Log
Yes, I could ignore ORA-001. But there are cases where I might want to know of ORA-001's occurring. Hemant At 11:59 PM 31-08-03 -0800, you wrote: Can't you tell your trigger to ignore this error as well. -Original Message- Sent: Saturday, August 30, 2003 7:19 AM To: Multiple recipients of list ORACLE-L Use a Database Trigger AFTER SERVERRROR. [careful, the table might fill up pretty quickly -- e.g at my site a load job ignores ORA-0001 errors but this table gets a lot of ORA-0001 errors !!] Try this : drop table system.oracle_errors; create table system.oracle_errors (db_username varchar2(30), db_session_id number, db_process_id number, unix_process_id varchar2(9), client_process_id varchar2(9), client_program varchar2(48), client_machine varchar2(64), client_terminal varchar2(30), client_os_user varchar2(30), appl_user_name varchar2(100), -- exclude if not using Oracle Apps appl_responsibility_name varchar2(100), -- exclude if not using Oracle Apps appl_form_name varchar2(80),-- exclude if not using Oracle Apps err_timestamp date, error_msg varchar2(2000)) tablespace customd -- or any other TBS storage (initial 4M next 1M maxextents 500 pctincrease 0) / create index system.oracle_errors_ndx1 on system.oracle_errors(appl_login_name,err_timestamp) tablespace customx -- or any other TBS storage (initial 256K next 256K maxextents 500 pctincrease 0) / create index system.oracle_errors_ndx2 on system.oracle_errors(err_timestamp) tablespace customx -- or any other TBS storage (initial 256K next 256K maxextents 500 pctincrease 0) / create index system.oracle_errors_ndx3 on system.oracle_errors(error_msg) tablespace customx -- or any other TBS storage (initial 1M next 1M maxextents 500 pctincrease 0) / drop trigger system.oracle_errors_trg; create or replace trigger system.oracle_errors_trg after servererror on database begin insert into system.oracle_errors select s.username, s.sid, p.pid, p.spid, s.process, s.program, s.machine, s.terminal, s.osuser, f.user_name, f.responsibility_name, f.user_form_name, -- exclude all three if not using Oracle Apps sysdate, dbms_utility.format_error_stack from apps.fnd_signon_audit_view f, v$session s, v$process p -- exclude apps.fnd_signon_audit_view where s.audsid = userenv( 'sessionid' ) and s.paddr=p.addr and p.pid=f.pid(+); -- exclude this join if not using Oracle Apps end; / At 06:19 PM 29-08-03 -0800, you wrote: >Is there anyway to setup oracle on the server side to log all fail and >error transaction in a file or something? I mean, error/fail >transaction due to, >eg: Integrity Contraint violation, Check constraint, Not Null constraint, any >other error. > >It would simply debugging since then we don't have to output / catch >and send error and SQL statement on the application level. > >System: ORACLE 9i on Redhat Linux 7.3 > >Thanks. >RDB >-- >Reuben D. Budiardja >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.net >-- >Author: Reuben D. Budiardja > INET: [EMAIL PROTECTED] > >Fat City Network Services-- 858-538-5051 http://www.fatcity.com >San Diego, California-- Mailing list and web hosting services >- >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the >message BODY, include a line containing: UNSUB ORACLE-L (or the name of >mailing list you want to be removed from). You may also send the HELP >command for other information (like subscribing). Hemant K Chitale Oracle 9i Database Administrator Certified Professional My personal web site is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jack van Zanen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling o
RE: Error Log
Can't you tell your trigger to ignore this error as well. -Original Message- Sent: Saturday, August 30, 2003 7:19 AM To: Multiple recipients of list ORACLE-L Use a Database Trigger AFTER SERVERRROR. [careful, the table might fill up pretty quickly -- e.g at my site a load job ignores ORA-0001 errors but this table gets a lot of ORA-0001 errors !!] Try this : drop table system.oracle_errors; create table system.oracle_errors (db_username varchar2(30), db_session_id number, db_process_id number, unix_process_id varchar2(9), client_process_id varchar2(9), client_program varchar2(48), client_machine varchar2(64), client_terminal varchar2(30), client_os_user varchar2(30), appl_user_name varchar2(100), -- exclude if not using Oracle Apps appl_responsibility_name varchar2(100), -- exclude if not using Oracle Apps appl_form_name varchar2(80),-- exclude if not using Oracle Apps err_timestamp date, error_msg varchar2(2000)) tablespace customd -- or any other TBS storage (initial 4M next 1M maxextents 500 pctincrease 0) / create index system.oracle_errors_ndx1 on system.oracle_errors(appl_login_name,err_timestamp) tablespace customx -- or any other TBS storage (initial 256K next 256K maxextents 500 pctincrease 0) / create index system.oracle_errors_ndx2 on system.oracle_errors(err_timestamp) tablespace customx -- or any other TBS storage (initial 256K next 256K maxextents 500 pctincrease 0) / create index system.oracle_errors_ndx3 on system.oracle_errors(error_msg) tablespace customx -- or any other TBS storage (initial 1M next 1M maxextents 500 pctincrease 0) / drop trigger system.oracle_errors_trg; create or replace trigger system.oracle_errors_trg after servererror on database begin insert into system.oracle_errors select s.username, s.sid, p.pid, p.spid, s.process, s.program, s.machine, s.terminal, s.osuser, f.user_name, f.responsibility_name, f.user_form_name, -- exclude all three if not using Oracle Apps sysdate, dbms_utility.format_error_stack from apps.fnd_signon_audit_view f, v$session s, v$process p -- exclude apps.fnd_signon_audit_view where s.audsid = userenv( 'sessionid' ) and s.paddr=p.addr and p.pid=f.pid(+); -- exclude this join if not using Oracle Apps end; / At 06:19 PM 29-08-03 -0800, you wrote: >Is there anyway to setup oracle on the server side to log all fail and >error transaction in a file or something? I mean, error/fail >transaction due to, >eg: Integrity Contraint violation, Check constraint, Not Null constraint, any >other error. > >It would simply debugging since then we don't have to output / catch >and send error and SQL statement on the application level. > >System: ORACLE 9i on Redhat Linux 7.3 > >Thanks. >RDB >-- >Reuben D. Budiardja >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.net >-- >Author: Reuben D. Budiardja > INET: [EMAIL PROTECTED] > >Fat City Network Services-- 858-538-5051 http://www.fatcity.com >San Diego, California-- Mailing list and web hosting services >- >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the >message BODY, include a line containing: UNSUB ORACLE-L (or the name of >mailing list you want to be removed from). You may also send the HELP >command for other information (like subscribing). Hemant K Chitale Oracle 9i Database Administrator Certified Professional My personal web site is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jack van Zanen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want
Re: Error Log
Use a Database Trigger AFTER SERVERRROR. [careful, the table might fill up pretty quickly -- e.g at my site a load job ignores ORA-0001 errors but this table gets a lot of ORA-0001 errors !!] Try this : drop table system.oracle_errors; create table system.oracle_errors (db_username varchar2(30), db_session_id number, db_process_id number, unix_process_id varchar2(9), client_process_id varchar2(9), client_program varchar2(48), client_machine varchar2(64), client_terminal varchar2(30), client_os_user varchar2(30), appl_user_name varchar2(100), -- exclude if not using Oracle Apps appl_responsibility_name varchar2(100), -- exclude if not using Oracle Apps appl_form_name varchar2(80),-- exclude if not using Oracle Apps err_timestamp date, error_msg varchar2(2000)) tablespace customd -- or any other TBS storage (initial 4M next 1M maxextents 500 pctincrease 0) / create index system.oracle_errors_ndx1 on system.oracle_errors(appl_login_name,err_timestamp) tablespace customx -- or any other TBS storage (initial 256K next 256K maxextents 500 pctincrease 0) / create index system.oracle_errors_ndx2 on system.oracle_errors(err_timestamp) tablespace customx -- or any other TBS storage (initial 256K next 256K maxextents 500 pctincrease 0) / create index system.oracle_errors_ndx3 on system.oracle_errors(error_msg) tablespace customx -- or any other TBS storage (initial 1M next 1M maxextents 500 pctincrease 0) / drop trigger system.oracle_errors_trg; create or replace trigger system.oracle_errors_trg after servererror on database begin insert into system.oracle_errors select s.username, s.sid, p.pid, p.spid, s.process, s.program, s.machine, s.terminal, s.osuser, f.user_name, f.responsibility_name, f.user_form_name, -- exclude all three if not using Oracle Apps sysdate, dbms_utility.format_error_stack from apps.fnd_signon_audit_view f, v$session s, v$process p -- exclude apps.fnd_signon_audit_view where s.audsid = userenv( 'sessionid' ) and s.paddr=p.addr and p.pid=f.pid(+); -- exclude this join if not using Oracle Apps end; / At 06:19 PM 29-08-03 -0800, you wrote: Is there anyway to setup oracle on the server side to log all fail and error transaction in a file or something? I mean, error/fail transaction due to, eg: Integrity Contraint violation, Check constraint, Not Null constraint, any other error. It would simply debugging since then we don't have to output / catch and send error and SQL statement on the application level. System: ORACLE 9i on Redhat Linux 7.3 Thanks. RDB -- Reuben D. Budiardja -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reuben D. Budiardja INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Hemant K Chitale Oracle 9i Database Administrator Certified Professional My personal web site is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Error Log
Is there anyway to setup oracle on the server side to log all fail and error transaction in a file or something? I mean, error/fail transaction due to, eg: Integrity Contraint violation, Check constraint, Not Null constraint, any other error. It would simply debugging since then we don't have to output / catch and send error and SQL statement on the application level. System: ORACLE 9i on Redhat Linux 7.3 Thanks. RDB -- Reuben D. Budiardja -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reuben D. Budiardja INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
