Database Links Troubleshooting the ORA 2085 database link %

							                                                   Content       TEXT/X-
Doc ID: Note:210630.1
                                                   Type:         HTML
Subject:Database Links: Troubleshooting
                                                   Creation      12-SEP-
        ORA-2085 "database link %s
                                                   Date:         2002
        connects to %s"
                                                   Last
Type: PROBLEM                                                    17-MAR-
                                                   Revision
Status: PUBLISHED                                                2003
                                                   Date:

Purpose


To explain database link naming rules in an effort to avoid the error ORA-2085 "database link %
s connects to %s"



Scope


Customers and support analysts creating dblinks and troubleshooting the ORA-2085 error.




Database Links: Troubleshooting the 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.




References:


Oracle8i SQL Reference - Release 3 (8.1.7)