Triggers de-compile themselves

#1

Hi there.

We're running:

Oracle9i Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
"CORE 9.2.0.8.0 Production"
TNS for Solaris: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - 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 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

And it hasn't had this problem.

Any ideas?

--
Adam

#2

Oh... another thing.

Is there any way to detect the compilation state of a trigger (eg: whether
it's been compiled or awaiting to be compiled).

I didn't see anything in the USER_TRIGGERS view which flags that. I'm not
sure where else one might look.

(I haven't actually googled about this yet, so fair cop if the answer is
"RTFM": I am going to do that now, but whilst I was here I thought I'd ask.

Cheers.

--
Adam

#3

Oh... another thing.

Is there any way to detect the compilation state of a trigger (eg: whether
it's been compiled or awaiting to be compiled).

I didn't see anything in the USER_TRIGGERS view which flags that. I'm not
sure where else one might look.

(I haven't actually googled about this yet, so fair cop if the answer is
"RTFM": I am going to do that now, but whilst I was here I thought I'd ask.

Cheers.

--
Adam

#4

Oh... another thing.

Is there any way to detect the compilation state of a trigger (eg: whether
it's been compiled or awaiting to be compiled).

I didn't see anything in the USER_TRIGGERS view which flags that. I'm not
sure where else one might look.

(I haven't actually googled about this yet, so fair cop if the answer is
"RTFM": I am going to do that now, but whilst I was here I thought I'd ask.

Cheers.

--
Adam

#5

Oh... another thing.

Is there any way to detect the compilation state of a trigger (eg: whether
it's been compiled or awaiting to be compiled).

I didn't see anything in the USER_TRIGGERS view which flags that. I'm not
sure where else one might look.

(I haven't actually googled about this yet, so fair cop if the answer is
"RTFM": I am going to do that now, but whilst I was here I thought I'd ask.

Cheers.

--
Adam

#6

On Aug 7, 10:49*am, Adam Cameron <adam_j... (AT) hotmail (DOT) com> wrote:

Quote:

Oh... another thing.

Is there any way to detect the compilation state of a trigger (eg: whether
it's been compiled or awaiting to be compiled).

I didn't see anything in the USER_TRIGGERS view which flags that. *I'm not
sure where else one might look.

(I haven't actually googled about this yet, so fair cop if the answer is
"RTFM": I am going to do that now, but whilst I was here I thought I'd ask.

Cheers.

--
Adam