Re: lib. cache lock on a package freezing everything: how come ?

On Feb 13, 5:46 am, "Sébastien de Mapias" <sglrig..._at_gmail.com> wrote:
> Hi,
> On a 10g DB we experienced a problem with a session obviously
> holding a lock on a package, that started to block any other sessions
> trying to perform calls to this pkg. During this phase where
> everything
> was blocked the status in DBA_OBJECTS for this pkg body showed
> 'INVALID'. As I didn't have time enough to find the guilty (the
> blocking
> session) I restarted the instance, which freeed everything.
>
> Once rebooted, the status then showed 'VALID', and last_ddl_time
> showed the pkg hadn't been recompiled for months.
> When a session happens to lock a PL/SQL module that way, what
> is it trying to do ? There's no more compilation at runtime OK ?, so
> how come a pkg or a procedure can be held kind of exclusively
> without any other session being able to access it ? How could this
> module status read 'INVALID' (to then come back to a valid state
> by itself) ?
>
> Thanks for any enlightenments...
> Regards,
> SR

Normally the only time I would expect a lock on a package to stop anyone from executing it would be if the package was being recompiled.

You should be auditing changes to production objects so check to see if anyone shows as attempting to recompile the package in your audit trail.

Also check the alert log for any error messages.

HTH -- Mark D Powell -- Received on Wed Feb 13 2008 - 11:07:01 CST