Re: Circular Invalid Package Issue

From: Chris S <>
Date: Wed, 6 Aug 2008 10:24:38 -0700 (PDT)
Message-ID: <>

On Aug 6, 11:24 am, Mark D Powell <> wrote:
> On Aug 6, 10:30 am, Chris S <> wrote:
> > Running Oracle on RHEL
> > Here is my problem:
> > I have a package in a user schema (USER1) that reads data through a
> > user (not global) DBLINK.  I have another user schema (USER2) with the
> > same package that reads the same data over its own user DBLINK.
> > When I compile USER1's package body it invalidates USER2's package
> > body.  When I compile USER2's package body, it invalidates USER1's
> > package body.
> > It is compiling at runtime (lazy compiles) just fine so it has not
> > been an issue for me to this point.  I was just wondering why it would
> > invalidate the other packages when I am not making any modifications
> > to the remote object.
> What does DBA_DEPENDENCIES show when queried for each package?
> If you compile user1's package body the only way user2's package body
> should go invalid is if user2's package calls user1's package.
> HTH -- Mark D Powell --

There are definitely no calls from user1's package to user2's packages or visa-versa so that's not it. I do think I found the problem though. Both users share a call to a view in a different database. If I change that view to be local for both users then it does not invalidate each others packages.

I actually created a test package in each user schema to test it out and it works. Doing the same thing a different way seems to have made all the difference in the world.

The next question is should I really care if they are being recompiled when they run? The packages are called once an hour through the scheduler so performance isn't really an issue.

Thanks for the help Mark. Received on Wed Aug 06 2008 - 12:24:38 CDT

Original text of this message