Re: Invalid objects

Hi Margaret,

A recent real-world example :
Whenever you use Vision Jade to build application, almost all constraints are enforced by triggers (yuck!! I won't explain though here why that is terrible, but it is). All triggers call one specific common pl/sql procedure, so all triggers are dependent on that procedure. Whenever that procedure is recompiled (because of an upgrade etc) ALL triggers become invalid. This is the way Oracle designed it. Whenever that trigger fires, Oracle will notice the trigger is invalid and re-compile. Doing so, the issue will resolve itself. In the same application I have a few packages calling each other. With above background you could easily imagine what happens when one of the two is recompiled. This can be resolved by using alter package <package name> compile BODY, provided only the body changed. Other than that, I have seen instances of deadlocks caused by frequent validation of packages (in theory on every call) and of course in that case the package becomes invalid. It is advisable to pin packages in the shared pool you really use all the time.
exec dbms_shared_pool.keep('<Username>.<packagename>') should do that trick.

Hth,

--
Sybrand Bakker, Oracle DBA
Margaret Burwell <aj739_at_FreeNet.Carleton.CA> wrote in message news:7ukies$s83_at_freenet-news.carleton.ca...
>
> If I perform the query
>
> select owner||'.'||object_name, object_type from dba_objects
> where status = 'INVALID';
>
> I find a number of procedures, packages, package bodies and views are
> listed.
>
> What would cause an object to become invalid and what are the implcations
> for the application?
>
> Margaret
Received on Wed Oct 20 1999 - 09:57:10 CDT