After discussion with The Oracle Support following is the conclusion.
This appears to be the same problem as discussed in the following note and bug report:
– Note:215123.1 “Procedures Get Invalidated When Database Link Names are Identical”
– Bug:2485372 “OBJECTS SHARED SAME REMOTE OBJECT NAME INVALIDATES EACH OTHER”
The conclusion is that this is working as designed, and the explanation to the invalidations is as follows: When the remote
PL/SQL object is loaded, a callback is invoked to validate the object. For remo
te objects, this involves comparing the timestamp of the remote object against t
he timestamp of the object in cache. If they differ, the invalidation mechanism
is triggered.
The work around to avoid the invalidations is to use different database link names.
I have attached Bug description and a metalink note as below for your reference.
Thanks
Paresh
Bug No. 2629879
Filed 16-OCT-2002 Updated 19-DEC-2005
Product Oracle Server – Enterprise Edition Product Version 8.1.7.3
Platform Microsoft Windows 2000 Platform Version 5.0
Database Version 8.1.7.3 Affects Platforms Generic
Severity Minimal Loss of Service Status Not a Bug. To Filer
Base Bug N/A Fixed in Product Version No Data
Problem statement:
PROCEDURES GET INVALIDATED WHEN A DATABASE LINK NAMES ARE IDENTICAL
——————————————————————————–
*** 10/16/02 11:17 pm *** Bug : . . ========================= ========================= . PROBLEM: 1. Clear description of the problem encountered PROCEDURES GET INVALIDATED WHEN A DATABASE LINK names are IDENTICAL Steps to reproduce the problem. I have two different userids on database A. . connected as user1 : CREATE DATABASE LINK my_db_link CONNECT TO Remote_User USING ‘Remote_Db1’; CREATE OR REPLACE PROCEDURE Bug_Test IS NUM NUMBER := 0; BEGIN SELECT COUNT(*) INTO NUM FROM dual@my_db_link; END; / . connected as user2 : CREATE DATABASE LINK my_db_link CONNECT TO Remote_User USING ‘Remote_Db2’; CREATE OR REPLACE PROCEDURE Bug_Test IS NUM NUMBER := 0; BEGIN SELECT COUNT(*) INTO NUM FROM dual@my_db_link; END; / . The name of database link is the SAME for both user1 and user2 but they point to two different databases and so about the name of my procedures. Remote user is the same but, as said, in two different databases. When i compile this procedure connected as user1 the one owned by user2 gets invalidated and vice versa. So i cannot have both procedures valid. . . 2. Pertinent configuration information (MTS/OPS/distributed/etc) . Any Oracle Database . 3. Indication of the frequency and predictability of the problem . The problem occurs everytime the Ct. wants to perform the above operation. . 4. Sequence of events leading to the problem . Same as 1. . 5. Technical impact on you. Include persistent after effects. . None. . . . ========================= DIAGNOSTIC ANALYSIS: List the specific steps and results taken to diagnose the problem. . Same as 1. . ========================= WORKAROUND: . The db_links name should be different. . ======================== RELATED BUGS: None ========================= REPRODUCIBILITY: State if the problem is reproducible. Yes. . If so, has it been reproduced at the customer’s site or in-house? . In Customer’s Site as well as Inhouse. The above test case is produced in house. . If it is reproducible at the customer’s site, is it only in the same database? only in the same schema? . It happens in all his databases with the above mentioned configuration. . Is it reproducible every time or intermittently? . Every Time . ========================= TESTCASE: A test case should be simple and standalone. . 1. Provide complete instructions in a readme file. This should be a step-by-step procedure, including the expected results for each step. . Same as Step 1 above. . 2. Indicate all resource requirements (i.e. tablespace of 500M) . None . 3. Indicate the expected results for the problem and the correct behavior. Suggest provide a log of executing the testcase. . The above test case (as described in Pt. 1) should not display invalid :- . . 4. Provide all files, even files such as sample.c . NA . ========================= DIAL-IN INFORMATION: . . ================================================= IMPACT DATE: . The Ct. does not want the procedure to be invalid. So, the Ct. wants the resolution of this problem ASAP. ================================================= . *** 10/17/02 12:53 am *** (CHG: Comp->RDBMS) *** 10/17/02 01:31 am *** (CHG: Sta->16 Asg->NEW OWNER) *** 10/17/02 01:35 am *** Related bug 2485372 *** 10/17/02 02:15 am *** (CHG: Sta->10) *** 10/17/02 02:15 am *** *** 10/21/02 10:43 pm *** (CHG: Sta->32) *** 10/21/02 10:43 pm *** *** 03/08/04 09:09 pm *** (CHG: SubComp->DICTIONARY) Doc ID: Note:215123.1 Subject: Procedures Get Invalidated When Database Link Names are Identical Type: PROBLEM Status: ARCHIVED Content Type: TEXT/X-HTML Creation Date: 17-OCT-2002 Last Revision Date: 09-JUL-2005
Fact(s)
~~~~~~~~
You are using Oracle Server Enterprise Edition 8.1.7.3.0.
Symptom(s)
~~~~~~~~~~
Your procedures are invaliedate when database link names are identical.
Steps to reproduce the problem:
1. Create two different userids on database A, with GLOBAL_NAMES=FALSE.
2. Connected as user1:
CREATE DATABASE LINK my_db_link
CONNECT TO Remote_User
IDENTIFIED BY password
USING ‘Remote_Db1’;
CREATE OR REPLACE PROCEDURE Bug_Test IS
NUM NUMBER := 0;
BEGIN
SELECT COUNT(*)
INTO NUM
FROM dual@my_db_link;
END;
/
3. Connected as user2:
CREATE DATABASE LINK my_db_link
CONNECT TO Remote_User
IDENTIFIED BY password
USING ‘Remote_Db2’;
CREATE OR REPLACE PROCEDURE Bug_Test IS
NUM NUMBER := 0;
BEGIN
SELECT COUNT(*)
INTO NUM
FROM dual@my_db_link;
END;
/
The name of database link is the SAME for both user1 and user2, but they point
to two different databases.
The remote user is the same but, as said, in two different databases. When we
compile Bug_Test procedure connected as user1, the procedure owned by user2
gets invalidated, and vice versa. Thus we cannot have both Bug_Test procedures
valid at the same time.
Cause
~~~~~~~
When the database links’ names are identical in such scenario, procedure status
is set to invalid. This is an expected behavior, as per bug report
Bug 2629879, which was closed by Development as not a bug.
Fix
~~~~
Use different database link names.
References
~~~~~~~~~~
Bug 2629879 PROCEDURES GET INVALIDATED WHEN A DATABASE LINK NAMES ARE
IDENTICAL
Bug 2485372 OBJECTS SHARED SAME REMOTE OBJECT NAME INVALIDATES EACH OTHER