0

I have "PROD-1" db which contains 2 schemas "Schema-1", "Schema-2" I created the private dblink "DBLINK-1" in both the schemas & they are pointing to different targets. here is my configuration

DB Name -> Source Schema -> Target DB -> Target Schema -> Dblink Name

PROD-1 -> Schema-1 -> TPRD-1 -> TSchema-1 -> DBLINK-1

PROD-1 -> Schema-2 -> TPRD-2 -> TSchema-2 -> DBLINK-1

I am using this dblink in procedure and deployed in both Schema-1 & Schema-2 when I compile the proc in Schema-1 it is invalidating Schema-2 proc & vice versa. here is the sample proc

CREATE OR REPLACE procedure test_dblink

is v_cnt number;

begin
   select count(*) into v_cnt from DUAL@DBLINK-1;

end;

as per my understanding, DBLINK-1 is a private dblink for the Schema-1 & Schema-2; therefore it should not conflict with each other. however, when I executed the procedures at the same time in both schemas, one procedure exectued successfully & other one was waiting for first one to complete then complete.

this is what may be happening

  1. Schema-1 proc started first and Proc status is Valid. Schema-2 proc was waiting
  2. Schema-1 proc completed now. Schema-2 proc came out of wait mode & invalidated the DBLink-1 in Schema-1. therefore Schema-1 proc is now Invalid status
  3. Schema-2 proc run and complete. Schema-2 proc is Valid now

My question is, how can I manage the same DBLink in two different schema of same DB without this conflict?

Appreciate you help

Lava
  • 1
  • 1
  • 4
  • this is bad practice to name objects the same in different schema's but ... you are right - dblinks are private objects. are you creating the procedures from the user it self ? (i mean , you ran the `create procedure` while logged on as schema-1 ? ) – haki Apr 11 '13 at 07:22
  • Hello Haki, thanks for your response. Finally I found the problem by creating the P1 ticket with Oracle. Bug 7395995 – Lava Apr 17 '13 at 19:22

1 Answers1

0

We solved that using Synonyms. Use unique names for your db links. Then create synonyms in each schema for each remote object that you use. This way you can still use identical PL/SQL code in both schemas.

u1957752
  • 26
  • 2