How to create a DB link between two oracle instanc

Question

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.

Answer 1

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

Answer 2

as a simple example:

CREATE DATABASE LINK _dblink_name_
  CONNECT TO _username_
    IDENTIFIED BY _passwd_
      USING '$_ORACLE_SID_'

for more info: http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5005.htm

Answer 3

Create database link NAME connect to USERNAME identified by PASSWORD using 'SID';

Specify SHARED to use a single network connection to create a public database link that can be shared among multiple users. If you specify SHARED, you must also specify the dblink_authentication clause.

Specify PUBLIC to create a public database link available to all users. If you omit this clause, the database link is private and is available only to you.

Answer 4

After creating the DB link, if the two instances are present in two different databases, then you need to setup a TNS entry on the A machine so that it resolve B. check out here

Answer 5

Creation of DB Link

CREATE DATABASE LINK dblinkname
CONNECT TO $usename
IDENTIFIED BY $password
USING '$sid';

(Note: sid is being passed between single quotes above. )

Example Queries for above DB Link

select * from tableA@dblinkname;

insert into tableA(select * from tableA@dblinkname);