CODEWITHSUNDEEP
oracle-database
J.Col
J.Col

Reputation: 41

Connect oracle database to another database

I am trying to connect oracle db to oracle db I tried to create database link on toad like this.

CREATE DATABASE LINK boston
  CONNECT TO admin IDENTIFIED BY 'mypassword' 
  USING 'host=192.168.1.65 dbname=sales';

It is created with no error but not working properly.

I need working "create database link" format with using ip address and service name.

Oracle Host to connect ip: 192.168.1.65 
oracle version: 10g 
Service name: xe 
Table name: sales

Upvotes: 0

Views: 865

Answers (1)

Popeye
Popeye

Reputation: 35900

You need to provide the proper connection string as follows:

CREATE DATABASE LINK boston
    CONNECT TO admin IDENTIFIED BY mypassword
    USING '(DESCRIPTION=
                (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.65)(PORT=1521))
                (CONNECT_DATA=(SERVICE_NAME=sales))
            )';

The best practice is to add the connection string in the tnsnames.ora 

SALES =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.65)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = sales)
    )
)

then use this tns alias in the DB link as following:

CREATE DATABASE LINK boston
    CONNECT TO admin IDENTIFIED BY mypassword
    USING 'SALES';

Cheers!!

Upvotes: 1

Related Questions