Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Running with _system_trig_enabled set to false

2,118 views
Skip to first unread message

Joe Fischer

unread,
Jan 27, 2003, 7:02:29 PM1/27/03
to
There were some posts back in 08/2002 about getting messages
like "ORA-04045 ORA-04045: errors during recompilation/revalidation of
SYS.CDC_ALTER_CTABLE_BEFORE." A solution was posted about doing:

-- disable system triggers
alter system set "_system_trig_enabled"=false;
@?/rdbms/admin/catalog
@?/rdbms/admin/catproc
-- re-enable system triggers
alter system set "_system_trig_enabled"=true;

This is on a 9.2 database. When I set system_trigger back to
true, the original problem shows up whenever somebody tries to connect
to the database. If I leave it set to false, everything seems to work
OK. Is this normal OK?
---
Joe

Vladimir M. Zakharychev

unread,
Jan 28, 2003, 4:18:38 AM1/28/03
to
Having system triggers disabled in regular mode of operation (that is,
not during upgrade or patch set install) is not ok in my books, so
this is NOT normal. I believe that one more step is necessary before
flipping system triggers back to enabled: running ?/rdbms/admin/utlrp.sql
to forcibly revalidate all PL/SQL modules. This should revalidate
dependencies for system triggers without them trying to kick in.

--
Vladimir Zakharychev (b...@dpsp-yes.com) http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"Joe Fischer" <jfis...@insight.rr.com> wrote in message
news:3e35c721....@news-server.insight.rr.com...

Mark D Powell

unread,
Jan 28, 2003, 10:30:02 AM1/28/03
to
"Vladimir M. Zakharychev" <b...@dpsp-yes.com> wrote in message news:<b15htk$11f$1...@babylon.agtel.net>...


Joe, if you have metalink support look up the following documents:
(bug no) 2187432, Doc Id 143639.1, 222467.1, and 2670738.1.

The bug appears to match your problem since it appears to relate to
JAVA code.

As far as turning off database event triggers goes: This is a feature
of Oracle that you do not have to use and if your shop does not now or
intend to use the feature then turning it off should be fine.

HTH -- Mark D Powell --

Paul Brewer

unread,
Jan 28, 2003, 4:03:38 PM1/28/03
to
"Mark D Powell" <Mark....@eds.com> wrote in message
news:2687bb95.03012...@posting.google.com...

>
> Joe, if you have metalink support look up the following documents:
> (bug no) 2187432, Doc Id 143639.1, 222467.1, and 2670738.1.
>
> The bug appears to match your problem since it appears to relate to
> JAVA code.
>
> As far as turning off database event triggers goes: This is a feature
> of Oracle that you do not have to use and if your shop does not now or
> intend to use the feature then turning it off should be fine.
>

I would be very wary of running in this mode.

We are on 8.7.1.4 and have a THIRD_PARTY three-tier application. All the
developers know the Oracle Username/Password which the application server
uses to connect to the database - and it would be useless to change it.

All the business rules are in the app. There are no constraints in the
database (other than primary keys) and I have *no* influence here.

The best I've been able to do so far is to restrict that Oracle username to
logging in from named terminals (i.e. the application servers), using an
after_event logon trigger owned by SYSTEM.

Any suggestions would be welcomed.

Regards,
Paul

Mark D Powell

unread,
Jan 29, 2003, 9:47:39 AM1/29/03
to
"Paul Brewer" <pa...@paul.brewers.org.uk> wrote in message news:<3e36f...@mk-nntp-1.news.uk.worldonline.com>...

Paul, I think you need database event triggers, but most shops do not
use them so turning them off until a bug patch is available is not
going to affect anything. In your case if you have hit this problem
then you probably need to file an iTAR to get Oracle's help. It
appears from one of the documents I listed that the problem is JAVA
related and removal of the code might be the solution, that or
re-validation of the invalid referenced code/object from a trigger.
There were several other documents, but I listed the ones with recent
dates that looked like they would apply.

IMHO -- Mark D Powell --

Paul Brewer

unread,
Jan 30, 2003, 3:15:31 PM1/30/03
to

Mark,

Sorry, I don't understand. File an iTAR? Why? I don't have an Oracle
problem, I have an application design problem, which thanks to Oracle's
database event triggers I can largely circumvent.

I was just trying to warn against disabling event triggers on a systemwide
basis. OK, I admit many may not use them, but some do.

But I have reread your caveat: "if your shop does not now or intend to use
the feature then turning it off should be fine" (sic)

That's fair enough.

Cheers,
Paul

khan...@gmail.com

unread,
Jul 4, 2014, 8:43:34 AM7/4/14
to
Certainly helpful. Do you know its an Hidden parameter and you wont be able to see anywhere in order to check the status before you perform update.

This may be helpful.

SELECT
a.ksppinm Param ,
b.ksppstvl SessionVal ,
c.ksppstvl InstanceVal,
a.ksppdesc Descr
FROM
x$ksppi a ,
x$ksppcv b ,
x$ksppsv c
WHERE
a.indx = b.indx AND
a.indx = c.indx AND
a.ksppinm LIKE '/_%' escape '/' AND A.ksppinm='_system_trig_enabled'
ORDER BY
1



Thanks.

Mladen Gogala

unread,
Jul 4, 2014, 7:01:13 PM7/4/14
to
On Fri, 04 Jul 2014 05:43:34 -0700, khans.jak wrote:

> On Tuesday, January 28, 2003 5:32:31 AM UTC+5:30, Joe Fischer wrote:

Please do not respond to the posts from 2003. The statute of limitation
expired on 0704/2004. Replying to posts older than 10 years is considered
a Usenet felony. Please, find the nearest DMV and turn in your driver
license.





--
Mladen Gogala
The Oracle Whisperer
http://mgogala.byethost5.com
0 new messages