Re: Can you create a database link back to same database instance?
- From: Romeo Ybanez <raybanez@xxxxxxxxx>
- To: nigel.cl.thomas@xxxxxxxxxxxxxx
- Date: Wed, 11 Mar 2009 01:09:25 -0700 (PDT)
Hi All,
Thanks for you reply. I found out that the problem lies with non-standard
Oracle directory configuration and the TNS_ADMIN is not set. Oracle cannot find
the tnsnames.ora to locate the database to connect.
________________________________
From: Nigel Thomas <nigel.cl.thomas@xxxxxxxxxxxxxx>
To: raybanez@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Sent: Tuesday, March 10, 2009 11:52:46 PM
Subject: Re: Can you create a database link back to same database instance?
Romeo
Yes, there's no problem creating loop-back database links - very common (we
used to use DB links to spread workload across multiple databases in Oracle 7 /
8; works fine in test with loopback then and now). When you have both schemas
in the same instance, you can't use a PUBLIC database link - because both
schemas see the same link, so one of them will inevitably be wrong (or fail to
create).
How about replacing it with a private database link (in at least one of the
schemas)?
Regards Nigel
2009/3/10 Romeo Ybanez <raybanez@xxxxxxxxx>
Hi,
I have this application that archives data to a different schema on the same
instance using database link. This is probably due to the fact that in the
Production environment it is archived to a different instance. Two schemas
are using database links to connect to themselves. One successfully connects
using the same service name to itself but the other does not. I am wondering
what the trick the schema has while the other one can't connect.
Example: In tnsnames.ora two service names are defined DB1 and DB2 (these are
also the SID's)
At DB1 instance a public database link is created.
CREATE PUBLIC DATABASE LINK arcdb CONNECT TO arc_db IDENTIFIED BY .....
USING "DB1";
This works. But in the other instance,
CREATE PUBLIC DATABASE LINK arcdb CONNECT TO arc_db IDENTIFIED BY ...
USING "DB2";
doesn't work.
So it seems that you can create a database link back to the same database
instance. If not I wonder what the trick by the other db?
Thanks.
Romeo Ybanez
--
//www.freelists.org/webpage/oracle-l
Other related posts:
