Oracle Database Link to the External Server from Current Server

To create database link from Server A to Server B (External Server), follow the below approach:

DATABASE LINK CREATION SYNTAX:

CREATE DATABASE LINK <DB_LINK_SERVER_B> CONNECT TO <B_SERVER_USER> IDENTIFIED BY <B_SERVER_USER_PWD> USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<serverB.domain@company.com>)(PORT=<1234>))(CONNECT_DATA=(SID=<SERVER_B_SID>)))';

ex: CREATE DATABASE LINK EXT_SERVER_B CONNECT TO USER_B IDENTIFIED BY USER_B_PWD USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=serverB.domain@company.com)(PORT=1234))(CONNECT_DATA=(SID=B_SID)))';

Test the connection from server A:

SQL> SELECT * FROM DUAL@EXT_SERVER_B;

DUMMY
--------
X  => This result is from Server B

Note:  If DB LINK is already available in the database for the external server then drop the existing DB LINK first and then re-create the new entry.

DROP DATABASE LINK <DB_LINK_SERVER_B>;

If you're planning to re-create the DB LINK using the existing TNS server property with different DB LINK name which you've dropped then follow the below steps to achieve that, otherwise you'll end up in ORA-02085: database link string connects to string error.

A. Connect to SQL and check the global_names value

SQL> show parameter global_names

NAME              TYPE     VALUE
----                   ----          -----
global_names  boolean  TRUE

B. Change the global_names parameter property to FALSE to allow the system to set the different DB LINK name for the old TNS server entry with you need to change

SQL> alter system set global_names=FALSE scope=both;

System altered.

SQL> show parameter global_names

NAME              TYPE     VALUE
----                    ----        -----
global_names  boolean  TRUE

C. Drop the database link and re-create the database link.

SQL> DROP DATABASE LINK <DB_LINK_SERVER_B>;

Database link dropped.

SQL> CREATE DATABASE LINK <DB_LINK_SERVER_B1> CONNECT TO <B_SERVER_USER> IDENTIFIED BY <B_SERVER_USER_PWD> USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<serverB.domain@company.com>)(PORT=<1234>))(CONNECT_DATA=(SID=<SERVER_B_SID>)))';

Database link created.

Now check the DB LINK functionality by using the command from the current server A,

SQL> SELECT * FROM DUAL@<DB_LINK_SERVER_B1>;

DUMMY
-----
X