RE: Suppress Logon Trigger for DBMS_JOB
- To: <charlottejanehammond@xxxxxxxxx>, "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 14 Oct 2005 12:32:56 -0400
Hi, I dont know if you received my previous post since I lost posting priv for
some days. Theres another bug regarding oracle Logoff Triggers. Before doing
Logoff Triggers, PLS READ FOLLOWING NOTE!!!! (Note:2893546.8)
===================================================================
Bug 2893546 OERI:[4412] when BEFORE LOGOFF trigger enabled
This note gives a brief overview of bug 2893546.
Affects:
Product (Component) Oracle Server (Rdbms)
Range of versions believed to be affected Versions < 10G
Versions confirmed as being affected 9.2.0.4
Platforms affected Generic (all / most platforms affected)
Fixed:
This issue is fixed in 9.2.0.5 (Server Patch Set) 10g Production Base Release
Symptoms:
ORA-600 [4412]
Related To:
Triggers
Description
ORA-600 [4412] can occur when there is an enabled
BEFORE LOGOFF TRIGGER which contains a ROLLBACK statement.
Workaround:
Do not call ROLLBACK in a logoff trigger.
===================================================================
This has an awful effect on DBMS_JOB engine, it provoked all my jobs to run at
same time raising CPU to 100%.
Hope you find this information useful.
regards,
GAP
-----Original Message-----
From: Aragon, Gabriel (GE Commercial Finance)
Sent: Viernes, 07 de Octubre de 2005 09:49 a.m.
To: 'charlottejanehammond@xxxxxxxxx'; ORACLE-L
Subject: RE: Suppress Logon Trigger for DBMS_JOB
I just implemented a couple of logon and logoff triggers on my db's. Big
mistake. All my jobs failed with:
ORA-12012: error on auto execute of job xxx
ORA-01427: single-row subquery returns more than one row
ORA-06512: at line 6
take a moment to read metalink for "ORA-1427" error, you will find a thread
from Deborah Lorraine on 27-May-04 regarding this issue, basically the idea is
don't fire this trigger for users connectd as SYSDBA.
hth
GAP
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Charlotte Hammond
Sent: Jueves, 06 de Octubre de 2005 11:34 a.m.
To: ORACLE-L
Subject: Suppress Logon Trigger for DBMS_JOB
Hi All,
We have an after logon trigger on our database. This
trigger also gets fired by DBMS_JOBs whenever they run
which we don't want.
Is there a neat way to prevent this? I was thinking
of checking V$SESSION.TYPE within the trigger to
detect the BACKGROUND processes but it seems to be
null at the time the trigger fires. Another option
was to check V$SESSION.PROGRAM but this was getting
messy. Any better ideas?
Thanks
Charlotte
PS. Oracle 9.2
__________________________________
Yahoo! Mail - PC Magazine Editors' Choice 2005
http://mail.yahoo.com
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
