Re: Which sessions hold state on which packages

  • From: Tanel Poder <tanel@xxxxxxxxxx>
  • To:
  • Date: Sat, 20 Feb 2010 02:24:51 +0800

X$KGLLK helps you to find any library cache objects which are open by a
session (or in session cursor cache which is pretty much the same as being
open) or which were open (but flushed out/invalidated meanwhile, thus their
locks are broken).

It doesn't asnwer exactly the OPs question, but if you want to know which
sessions have invalid package states after some DDL has happened, then I
once wrote a little query here:,7

It doesn't work on my 11.2 database, but I've used it on 10.2 successfully

Tanel Poder

On Sat, Feb 20, 2010 at 2:18 AM, Nigel Thomas <> wrote:

> Kurt-Franke
> I *think *Michael was looking for packages where the caller isn't
> necessarily "in" the package, but the caller's session has initialised some
> package state, and the caller's session hasn't ended yet. As you know, if
> user A has active state in package P, then if another user recompiles the
> package (maybe as a result of dependency-based invalidations) then user A
> will see the notorious “ORA-04068: existing state of packages has been
> discarded”.
> NB there's a reference to this (which helped me remember the error number)
> at Eddie Awad's blog here:
> along with techniques for avoiding ORA-4068.
> Regards Nigel
> On 19 February 2010 17:43, Kurt Franke <Kurt-Franke@xxxxxx> wrote:
>> Mathias,
>> I assume you don't do the select excatly at the time your very short
>> package ist active.
>> After it has finished it will no longer occure in v$code_object_in_use

Other related posts: