How to create a DB link between two oracle instances

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

User · Answer

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.

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[oracle] How to create a DB link between two oracle instances

Examples related to oracle

Examples related to dblink