Re: Getting ora-04068 errors intermittently

Hi,

Excerion schrieb:
> the error itself is always about one particular package in the database
> that i've made. Now the common cause for this type of error from
> what i've read is that the package was recompiled at the sametime as a
> user was using the package.

On our db compile and use in parallel is not leading to this error. Instead there are two possible situations: 1. Timeout if object itself is used.
2. Sessions using dependent objects are getting much lock and pin waits on the used objects. This is caused by automatic invalidation of all depending objects when eg. altering a table. If there are at least two sessions trying to recompile there could be problems (both are pinning, no one can lock). The sessions will time out, but this could be a very long time to get the db running again.
Because of 2 we are *very* cautious on doing any ddl causing invalidations in normal operation.

Also we are facing same/likely problem too (Database 8.1.7.4, yes we will migrate to 9.2.0.6 soon).
We have some packages depending on each other. Sometimes we get this error. I could not put the finger on it yet, but as far as I suspect it's as follows:

1. There are two (at least) packages (a,b) depending on each other as a needs b.
2. body of b is replaced after create of a.
3. a and b is used previous to replace of body c. wht happens seems to be:
4. body of b is created new (but nor replaced in shared pool)
5. body of a is not compiled (and so no timestamp changed)
6. usage of a will use sp-version of a which is bound to sp-version of b, but b is changed!

So there are no invalid object, but until first execution of a there will be a sleeping devil.
If I am corret at the above (and I am *not* sure, just guessing) a flush of the shared pool after replacing any code will do it, but that should be done by the dba and not given to application development. In production systems it would be nearly impossible to do so without downtime.

Of course there are many ways the "replace" of the body b could happen. Each alter statement on dependencies could force this. At the time the error shows there is not much to be seen anymore (since the db has done most corrections in background). Only hint on something has happened is last_ddl_time in dba(all?)_objects.

Any suggestion on my guess are welcome.
>
> Unfortunately this cannot be the case for me since the database has
> always been untouched when the users are getting this error.
As I pointed out it could have been much earlier. But take a look at ddl-times of the packages.

greetings

Matthias Received on Thu Sep 01 2005 - 16:44:58 CDT