Create DB links

DB_LINK_APPS_TO_RPDCSSOA.HDNL.IT

CREATE DATABASE LINK DB_LINK_APPS_TO_RPDCSSOA.HDNL.IT CONNECT TO dcssoa IDENTIFIED BY dcssoarp USING 'RPCONNECT.HDNL.IT';

select count(1) from tab@DB_LINK_APPS_TO_RPDCSSOA.HDNL.IT;

=====================================================

http://psoug.org/reference/db_link.html

To explain database link naming rules in an effort to avoid the error ORA-2085 "database link %s connects to %s". Additionally, the effect of GLOBAL_NAMES=TRUE on the creation and use of database links. The naming rules that are described in this note are not applicable when GLOBAL_NAMES=FALSE.

Troubleshooting Steps

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>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 the 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.

The domain portion of the GLOBAL_NAME setting is usually set at db creation time and is derived from the value DB_DOMAIN. So the GLOBAL_NAME setting would be DB_NAME.DB_DOMAIN unless changed after the database creation time.