Triggers de-compile themselves
Date: Thu, 7 Aug 2008 16:47:39 +0100
Oracle9i Release 184.108.40.206.0 - 64bit Production
PL/SQL Release 220.127.116.11.0 - Production
"CORE 18.104.22.168.0 Production"
TNS for Solaris: Version 22.214.171.124.0 - Production NLSRTL Version 126.96.36.199.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 188.8.131.52.0 - Production
PL/SQL Release 184.108.40.206.0 - Production
CORE 220.127.116.11.0 Production
TNS for 32-bit Windows: Version 18.104.22.168.0 - Production NLSRTL Version 22.214.171.124.0 - Production
And it hasn't had this problem.
-- AdamReceived on Thu Aug 07 2008 - 10:47:39 CDT