Re: Database Links and Weird Behavior

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