To explain database link naming rules in an
effort to avoid the error ORA-2085 "database link %s connects to %s"
When the source database initialization
parameter GLOBAL_NAMES is set to true, the database link name must match the target
database global name as it exists in the GLOBAL_NAME view in the data
dictionary.
The GLOBAL_NAME can be determined by
logging in to the database with system privileges and issuing the following
command:
SQL>Select * from global_name;
Additionally, if you do not specify the
domain portion of the dblink name in the create statement, Oracle automatically
qualifies the link name with the domain of the SOURCE database global name
view.
Check the contents of ALL_DB_LINKS for the
fully qualified link name.
For example, if you defined a database link
in PROD.ORACLE.COM to connect to target instance TEST.WORLD.COM in the
following manner:
SQL>Create public database link TEST
connect to userid identified by password using test;
SQL>select * from tablename@TEST;
This select would yield the following
error:
ORA-2085 "database link
TEST.ORACLE.COM connects to TEST.WORLD.COM"
The correct syntax for defining the link
would be:
SQL>Create public database link
TEST.WORLD.COM connect to userid identified by password using test;
SQL>select * from
tablename@TEST.WORLD.COM;
Would yield desired result.
It is possible to alter the GLOBAL_NAME
table so that the domain portion of both SOURCE and TARGET global names are
identical. This would eliminate the need to include the domain in the create
database link statement.
In the above example, we could alter the
GLOBAL_NAME of TEST.WORLD.COM in the following manner:
Login to TEST with system privileges and
issue:
SQL>alter database rename global_name to
TEST.ORACLE.COM;
Now, the create database link statement
could also be changed.
Login to PROD.
SQL>create public database link TEST
connect to userid identified by password using test;
A database link would be defined in
ALL_DB_LINKS as TEST.ORACLE.COM.
SQL>select * from tablename@TEST;
This would yield the desired result.
