Re: Circular Invalid Package Issue

On Aug 6, 1:24 pm, Chris S <cjsom..._at_gmail.com> wrote:
> On Aug 6, 11:24 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
>
>
>
>
>
> > On Aug 6, 10:30 am, Chris S <cjsom..._at_gmail.com> wrote:
>
> > > Running Oracle 10.2.0.3 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.- Hide quoted text -
>
> - Show quoted text -

Your are welcome. I do not understand why compiling package body 2 would have any effect on package body 1 and the other other way around just because both packages share a select on the same remote object. We have packages that issue remote/distributed queries and we do not suffer from this problem on 9.2.0.6 or any prior release we have been on. If and when we ever get to 10.2 we will have to test for this.

• Mark D Powell --