Re: Disappearing Triggers!

smithari_at_hotmail.com (Arielle Smith) wrote in news:677227cc.0209071142.14576b10_at_posting.google.com:

> I posted a message recently about ORA-03113 errors and got some
> excellant advice (many thanks), but continuing to track the problem
> has uncovered some very bizarre happenings. Following a suggestion, I
> ran a count of my triggers and found over half of the 60+ missing. I
> ran a script to recreate them and thought I was out of the woods.
> Well, irony slapped me back down. While talking on the phone a couple
> hours later with a DBA explaining how we'd located the problem and how
> I could finally catch a decent night's sleep, the support phone starts
> ringing off the hook and, d*** the luck, another trigger had vanished
> and people were dropping connections whenever they tried to change
> data in the associated table. At that point, the only thing I could
> do was reboot since I couldn't recreate the trigger nor even analyze
> the table or index and even just shutting down and restarting Oracle
> had problems. This AM I checked again and yet another one was
> missing. This time, however, I was able to recreate the trigger
> without jumping through hoops, I believe because weekend users aren't
> likely to touch the associated table.
>
> Has anyone seen this before?? How on earth can I trap it to find out
> what's causing it besides turning on tracing for the whole DB? It is
> so random that I don't even have a clue when it will surface next. As
> an interesting and probably completely useless side note, it appears
> to be dropping them in alphabetical order....
>
> Thanks for any insight you can give.

I contend that triggers (or other objects) do NOT spontaneously go "poof" and disappear from an Oracle database. Not that we (TINW) doubt you, please post your evidence that the triggers were present & are now gone.

FWIW - I AUDIT all DDL statements so I can at least identfy after the fact who did what to whom & when. This level of AUDITing should not significantly burden your DB or system.

HTH & YMMV HAND! Received on Sat Sep 07 2002 - 17:46:32 CDT