RE: Monitoring occurence of snaphot too old

Use a SERVERERROR trigger to capture the ORA-01555 event and log it somewhere, I find the alert log is easiest. I have a script that picks up custom events written to the alert log and notifies me about them. Other than that follow the advice in other posts and try to avoid them all together. If you are below 9i then get rid of OPTIMAL on all your rollback segments so they never shrink and monitor them. Shrink them manually when system use is very low.

create or replace trigger tk$servererror after servererror on database declare

   mysession v$session%rowtype;
begin

• Some errors are not logged...
• 1 - Key violation.
• 1004 - Default user feature not supported.
• 1013 - Use canceled operation.
• 1017 - Invalid User Name/Password
• 1400 - Can not insert null.
• 1418 - Index does not exist.
• 1722 - Invalid number.
• 1747 - Invalid column name.
• 1839 - Date not valid.
• 4043 - Object does not exist.
• 6550 - PL/SQL Complilation Failure
  --
  if ora_server_error(1) not between 900 and 999 then if ora_server_error(1) not in (1,1004,1013,1017,1400,1418,1722,1747,1839,4043,6550) then select * into mysession from v$session where sid = (select distinct sid from v$mystat); sys.dbms_system.ksdind(0); sys.dbms_system.ksdwrt(2,'SERVERERROR[' || ora_server_error(1) || '] SESSION ' || mysession.sid || ',' || mysession.serial# || ' USER ' || mysession.username || ',' || mysession.osuser || ' PROGRAM ' || mysession.program || ',' || mysession.machine || ' CLIENT INFO ' || mysession.client_info); end if; end if; exception when others then dbms_output.put_line(dbms_utility.format_error_stack); end;

Ethan Post
perotdba (AIM), epost1 (Yahoo)

-----Original Message-----
[mailto:Mohammed.Ahsanuddin_at_VerizonWireless.com] Sent: Friday, August 02, 2002 11:14 AM
To: Multiple recipients of list ORACLE-L

Dear List members,

I was wondering if there is a way to monitor the database and tell if there is a possiblility of snapshot too old error occurence.

Any input is highly appreciated..

Thanks

Mohammed Ahsanuddin
Oracle DBA

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Mohammed.Ahsanuddin_at_VerizonWireless.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (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.com
-- 
Author: Post, Ethan
  INET: Ethan.Post_at_ps.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (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).