Re: Database Links and Weird Behavior

On Oct 18, 1:32 pm, joel garry <joel-ga..._at_home.com> wrote:
> On Oct 18, 12:10 pm, martin.c..._at_gmail.com wrote:
>
>
>
> > Greetings,
> > I was hoping that someone could explain to me the following behavior
> > in regards to db links....
>
> > Currently we have three development environments(schemas) set up on a
> > single database, same instance. Each of these environments is a
> > snapshot from our production environment at a given time.
>
> > Each of these environments has objects that reference data in a remote
> > database via private db link. The name of the db link is the same in
> > each environment.
>
> > In a nutshell
> > DEVA references REMOTEA
> > DEVB references REMOTEB
> > DEVC references REMOTEC
>
> > Here is the behaviour I'm seeing
>
> > I compile an object in DEVA that references REMOTEA, it invalidates
> > that same object in DEVB and DEVC
>
> > I compile the object in DEVB that references REMOTEB, now it
> > invalidates that object in DEVA (DEVC is still invalidated)
>
> > I compile the object in DEVC that references REMOTEC, now DEVA and B
> > objects are invalid.
>
> > DEVA, DEVB, and DEVC only have connect privledges and cannot reference
> > objects that they currently do not own.
>
> > Here are some details on the environments.
>
> > Dev Environment
> > Oracle9i Release 9.2.0.1.0 32bit (windows)
>
> > Remote Environment
> > Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit
>
> > DB Links
>
> > DEVA
> > create database link ORAAPPS.US.ORACLE.COM
> > connect to USER
> > identified by XYZ
> > using 'REMOTEA.ASP.COM';
>
> > DEVB
> > create database link ORAAPPS.US.ORACLE.COM
> > connect to USER
> > identified by XYZ
> > using 'REMOTEB.ASP.COM';
>
> > DEVC
> > create database link ORAAPPS.US.ORACLE.COM
> > connect to USER
> > identified by XYZ
> > using 'REMOTEC.ASP.COM';
>
> > Can someone shed some light on this? Is this expected behavior?
>
> > Thanks!
>
> > Chip Martin
>
> I'm pretty rusty on this so maybe I haven't got my head around it, but
> I think what is happening is that the using statement identifies the
> local link that the remote user uses, but the object you are
> referencing is owned by the common user USER. So since all three
> environments look at USER, invalidating it for one invalidates it for
> all. So that would be expected?
>
> jg
> --
> @home.com is bogus.
> telnetd[19824]: getpid: peer died: Error 0

Joel,
Thanks for the response.

I don't think I was clear, the object compiles successfully in the schema that I am working in, it invalidates the object with the same name in the other schema's. Each schema has no visibility into the other schema's, they can only reference their own objects. All three environments don't reference the same remote environment, they reference their own isolated remote environment using the same user.

So for clarity sake, lets assume we are connecting to SCOTT on the remote database(s)

DEVA
create database link ORAAPPS.US.ORACLE.COM connect to SCOTT
identified by TIGER
using 'REMOTEA.ASP.COM';

DEVB
create database link ORAAPPS.US.ORACLE.COM connect to SCOTT
identified by TIGER
using 'REMOTEB.ASP.COM';

DEVC
create database link ORAAPPS.US.ORACLE.COM connect to SCOTT
identified by TIGER
using 'REMOTEC.ASP.COM';

When I compile an object that references a remote object, It should not affect objects in an unrelated schema should it? Am I missing something? Received on Thu Oct 18 2007 - 17:25:06 CDT