Re: Implicit recompile ?

Implicit recompile only works for the first call if the dependencies are local (ie, all within the one database). If you;ve got a dblink, and a procedure in database A referencing a table in database B, then when you call the procedure, it doesn't know it's invalid yet.... so it hares off down the link, expecting to work, finds the SCN of the table has changed, invalidates itself and throws an error.

The *second* time that procedure is called, however, since it already now knows it is invalid, it hares off down the link, grabs the new SCN on the table and recompiles itself and executes normally.

So the obvious question is: is everything you are describing all within the one database, or are you using dblinks?

Regards
HJR "Richard Elliott" <richard.a.elliott_at_williams.com> wrote in message news:8c132b3c.0109190826.22053a93_at_posting.google.com...
> As I understand it, Oracle will mark all objects dependant on an
> object being altered invalid. No problem as Oracle will also
> automatically recompile any object marked invalid who's last changed
> date is prior to the time it was marked invalid (Implicit Recompile)
>
> I have a procedure that is called a lot. I'm sure it is being marked
> invalid when I change/alter one of the dependant tables or other
> dependant objects (normal behavior) , but the implicit recompile is
> either not happening or is failing. I suspect an attempt to recompile
> it is being executed before the dependant table or object has
> completed it's recompile(marked valid again) and thus fails.
>
> In every case where this has happened, I have to manually recompile
> the SP but I can not recompile it fast enough to avoid at least 1
> error trying to execute it from the application. Even when I follow
> the table alter with an alter compile for the SP.
>
> Is the implicit recompile a one-time shot ?
>
> How do you work around this if the SP has to be up 24/7 ?
>
>
> Any feedback would be much appreciated.
Received on Wed Sep 19 2001 - 20:09:17 CDT