| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Suppress Logon Trigger for DBMS_JOB
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)
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_at_yahoo.com'; 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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]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
 |
 |