jaic69
17 de Septiembre del 2004

Saludo a todos soy nuevo en este foro mi nombre es Jorge, tengo un problema con un database link en mi oficina, al tratar hacer un select * from tabla1@nombre_dblink, me sale este error ORA-02085: database link (NOMBRE_DBLINK) connects to (BD1). Cual sería la solución?

sys
17 de Septiembre del 2004

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 [email protected];

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)