Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to create a DB link between two oracle instances

Tags:

How to create a DB link between two Oracle instances. Let's say A and B are two instances. I want to access the data in instance B from the instance A.

like image 798
Arunkumar Avatar asked Nov 07 '12 09:11

Arunkumar


People also ask

What is an Oracle database link?

A database link is a pointer that defines a one-way communication path from an Oracle Database server to another database server. The link pointer is actually defined as an entry in a data dictionary table. To access the link, you must be connected to the local database that contains the data dictionary entry.

How can I join two databases in Oracle?

CREATE DATABASE LINK to_db1 CONNECT TO hr1 IDENTIFIED BY hr1 USING DB1; For this to work you got to have both databases with the right TNS, or else you got to use a connection string instead of the database service.


1 Answers

If you want to access the data in instance B from the instance A. Then this is the query, you can edit your respective credential.

CREATE DATABASE LINK dblink_passport CONNECT TO xxusernamexx IDENTIFIED BY xxpasswordxx USING '(DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=xxipaddrxx / xxhostxx ) (PORT=xxportxx)) (CONNECT_DATA= (SID=xxsidxx)))'; 

After executing this query access table

SELECT * FROM tablename@dblink_passport; 

You can perform any operation DML, DDL, DQL

like image 70
anandchaugule Avatar answered Oct 09 '22 02:10

anandchaugule