Why does an invalid trigger in one schema stop processing in another?
in [Oracle]
|
Prev: instructor solution manual for Introduction to Probability 2nd Ed by Bertsekas and Tsitsiklis
Next: Best way to validate cold datafiles
From: Robert Nicholson on 10 Jul 2010 12:20 So, yesterday I saw something disturbing in the log of one of our processes. We have a process that writes to data in schema A but it's log was complaining about an invalid trigger in schema B where there is no relationship defined that should create any dependency b/w these two schemas A and B. Why is it that when I was writing to objects in Schema A that it complained about an invalid trigger in Schema B when there is no relationship b/w these two schemas or sets of objects? The only relationship is that the objects and schemas are sharing the same tablespaces. This was in Oracle 11g RAC
From: Vladimir M. Zakharychev on 10 Jul 2010 16:32 On Jul 10, 8:20 pm, Robert Nicholson <robert.nichol...(a)gmail.com> wrote: > So, yesterday I saw something disturbing in the log of one of our > processes. We have a process that writes to data in schema A but it's > log was complaining about an invalid trigger in schema B where there > is no relationship defined that should create any dependency b/w these > two schemas A and B. > > Why is it that when I was writing to objects in Schema A that it > complained about an invalid trigger in Schema B when there is no > relationship b/w these two schemas or sets of objects? > > The only relationship is that the objects and schemas are sharing the > same tablespaces. > > This was in Oracle 11g RAC Well, there must be a dependency of some sort. I can think of these: a) an invalid trigger in Schema B on a table in Schema A. This one would be obvious. b) a trigger in Schema A issues a direct or an indirect (by calling a stored procedure in Schema B) DML on a table in Schema B, which in turn has an invalid trigger on that table. Synonyms might be involved. More details (like statements executed and errors returned) would be helpful for further analysis. Hth, Vladimir M. Zakharychev
From: Robert Nicholson on 11 Jul 2010 13:06 On Jul 10, 3:32 pm, "Vladimir M. Zakharychev" <vladimir.zakharyc...(a)gmail.com> wrote: > On Jul 10, 8:20 pm, Robert Nicholson <robert.nichol...(a)gmail.com> > wrote: > > > So, yesterday I saw something disturbing in the log of one of our > > processes. We have a process that writes to data in schema A but it's > > log was complaining about an invalid trigger in schema B where there > > is no relationship defined that should create any dependency b/w these > > two schemas A and B. > > > Why is it that when I was writing to objects in Schema A that it > > complained about an invalid trigger in Schema B when there is no > > relationship b/w these two schemas or sets of objects? > > > The only relationship is that the objects and schemas are sharing the > > same tablespaces. > > > This was in Oracle 11g RAC > > Well, there must be a dependency of some sort. I can think of these: > > a) an invalid trigger in Schema B on a table in Schema A. This one > would be obvious. > b) a trigger in Schema A issues a direct or an indirect (by calling a > stored procedure in Schema B) DML on a table in Schema B, which in > turn has an invalid trigger on that table. Synonyms might be involved. > > More details (like statements executed and errors returned) would be > helpful for further analysis. > > Hth, > Vladimir M. Zakharychev What was strange was that the trigger in question was only generating thru sequence the id of the table in schema a. and that has absolutely nothing to do with anything in schema b.
From: Vladimir M. Zakharychev on 11 Jul 2010 14:16
On Jul 11, 9:06 pm, Robert Nicholson <robert.nichol...(a)gmail.com> wrote: > On Jul 10, 3:32 pm, "Vladimir M. Zakharychev" > > > > <vladimir.zakharyc...(a)gmail.com> wrote: > > On Jul 10, 8:20 pm, Robert Nicholson <robert.nichol...(a)gmail.com> > > wrote: > > > > So, yesterday I saw something disturbing in the log of one of our > > > processes. We have a process that writes to data in schema A but it's > > > log was complaining about an invalid trigger in schema B where there > > > is no relationship defined that should create any dependency b/w these > > > two schemas A and B. > > > > Why is it that when I was writing to objects in Schema A that it > > > complained about an invalid trigger in Schema B when there is no > > > relationship b/w these two schemas or sets of objects? > > > > The only relationship is that the objects and schemas are sharing the > > > same tablespaces. > > > > This was in Oracle 11g RAC > > > Well, there must be a dependency of some sort. I can think of these: > > > a) an invalid trigger in Schema B on a table in Schema A. This one > > would be obvious. > > b) a trigger in Schema A issues a direct or an indirect (by calling a > > stored procedure in Schema B) DML on a table in Schema B, which in > > turn has an invalid trigger on that table. Synonyms might be involved. > > > More details (like statements executed and errors returned) would be > > helpful for further analysis. > > > Hth, > > Vladimir M. Zakharychev > > What was strange was that the trigger in question was only generating > thru sequence the id of the table in schema a. > > and that has absolutely nothing to do with anything in schema b. Wait a sec: you initially stated that the trigger is in schema B. And it generates an id for a table in schema A from a sequence. And this trigger is invalid. Is this correct? If not, please provide more details on your setup. Regards, Vladimir M. Zakharychev |