Triggers de-compile themselves

From: Adam Cameron <>
Date: Thu, 7 Aug 2008 16:47:39 +0100
Message-ID: <4q54uu8uv9rq$.1m2ylxzcr7ki9$>

Hi there.

We're running:

Oracle9i Release - 64bit Production PL/SQL Release - Production
"CORE Production"
TNS for Solaris: Version - Production NLSRTL Version - Production

(that's from select * from v$version;)

A web application connects to database on the above via JDBC. In general it's running normal website stuff: basic CRUD functionality with a mix of SQL and procedures. Nothing outrageous or complex.

We'd locked the system down for maintenance today - only user on the system
(me) - and whilst running a script, I noticed (via SQL Developer) a bunch
of triggers switching from "all good" to "needs compiling". There's nowt wrong with them, simply recompiling them again sorts them out.

The maintenance script was the only thing running which was hitting the DB, and all it was doing was a few SELECT and UDPATE queries. These fire off some (different) triggers which call a procedure which does a SELECT or two, and an INSERT. The detail is probably irrelevant (?) other than the fact that there's no schema changes going on, so there should be nothing happening that might cause a trigger to need recompiling.

We're not sure if the script being run has anything to do with it, or it was just coincidence that we happened to be looking @ these triggers as we were running it (if you see what I mean).

Also interesting is that the triggers that decided they need to be recompiled had *nothing* to do with any of the resources being hit by this script.

We can't reliably replicate this, but we have seen it happen once before.

I'm a web developer not a DB, and my Oracle knowledge extends to being able to install it, runs some basic DML queries and write the odd procedure - provided I have a guide book handy. I'm out of my depth with this sort of thing.

I asked the DBA bloke if he'd seen anything like this, and he pretty much ecoed what I would have thought: the triggers should just sit and behave unless some object they reference gets changed in such a way that the trigger won't work. So he's stumped too (although he didn't spend much time looking at it).

We have the same application running in a slightly different environment:

Oracle9i Enterprise Edition Release - Production PL/SQL Release - Production
CORE Production
TNS for 32-bit Windows: Version - Production NLSRTL Version - Production

And it hasn't had this problem.

Any ideas?

Received on Thu Aug 07 2008 - 10:47:39 CDT

Original text of this message