CODEWITHSUNDEEP
oracle-database
Mike
Mike

Reputation: 2339

ORA-04045: errors during recompilation/revalidation

I just suddenly started getting this error when trying to query the table AVC.

ORA-04045: errors during recompilation/revalidation of PUBLIC.AVC
ORA-04098: trigger 'TTMS.ALTERED_TTMSDB_TABS_TRIGGER' is invalid and failed re-validation

select * from avc doesnt work but select * from exfc.avc does work.

Can anyone tell me what is going on?

Upvotes: 3

Views: 53752

Answers (2)

Dave Costa
Dave Costa

Reputation: 48121

Presumably there is not an object in your schema named AVC. When you reference AVC without a schema qualifier, it is therefore accessing the PUBLIC object with that name. To find out what kind of object that is, SELECT object_type FROM all_objects WHERE object_name='AVC' AND owner='PUBLIC'.

Whatever it is, querying it is for some reason causing a trigger to fire. Maybe this is some sort of auditing function. But currently the trigger is invalid. This could mean that someone modified the trigger itself but made an error; or it could mean that someone dropped or modified some other object that the trigger depends on, in such a way that the trigger code is no longer valid.

So, whoever's responsible for that trigger needs to find out what error is occurring in its compilation and resolve it.

When you explicitly query EXFC.AVC, you are bypassing the PUBLIC object and going directly to some underlying object, probably an actual table. It seems very odd that you would have access to this, since the whole point of routing access through a public synonym is usually to prevent direct access to the object. And if, in fact, the purpose of the trigger is to audit accesses to the table, then allowing you to bypass it entirely is a pretty big design flaw.

Upvotes: 6

kite_runner90
kite_runner90

Reputation: 1

Yes, it basically tells you that the trigger mentioned has an error. Easiest way I could fix this since I was working on SQL developer was,

Open up 'Triggers' tab and search for this trigger. Compile it and run your query again. works perfectly fine :)

Upvotes: 0

Related Questions