How to create a DB link between two oracle instances

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

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

There are couple of ways to create db link:

1. Using tnsnames:

Step1:

Add target database service details to tns names of source:

 <addressname> =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(Host = <hostname of target db>)(Port = <port of target db listener>))
    )
  (CONNECT_DATA =
    (SERVICE_NAME = <service name of target db>)
  )
 )

Step 2:

Database links can be public or private. A public database link is one that can be used by any user. A private database link can be used only by the database link’s owner.

Below are the commands to create both private and public databse links:

Public database link

CREATE DATABASE LINK dblink CONNECT TO IDENTIFIED BY USING '(DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=port of target db listener)) (CONNECT_DATA=(SERVICE_NAME=)) )';

Private database link

connect to the user schema in which we need a private db link:

CREATE  DATABASE LINK <dbklink name>
CONNECT TO <target db userschemaname>
IDENTIFIED BY <target db userschemapassword>
USING '<addressname defined in tns>';

2. Using complete connection string.

CREATE DATABASE LINK dblink 
    CONNECT TO <target db userschemaname> IDENTIFIED BY <target db userschemapassword>
    USING '(DESCRIPTION=
                (ADDRESS=(PROTOCOL=TCP)(HOST=<hostname of target db>)(PORT=port of target db listener))
                (CONNECT_DATA=(SERVICE_NAME=<service name of target db>))
            )';
            

Steps are same for cdb configuration

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);

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

CREATE DATABASE LINK remote_db
CONNECT TO remote_username IDENTIFIED BY remote_password
USING '192.168.0.100:1521/orcl';

Replace db_link_name with the name you want to give your database link, username and password with the credentials for the remote database, and hostname:port/service_name with the connection details for the remote database.

For example, if the remote database is hosted on the server with IP address 192.168.0.100, listening on port 1521, and has a service name of "orcl", the statement would look like this:

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.

Create Oracle Database link query::

CREATE DATABASE LINK "SPLHNDLGLINK"
   CONNECT TO "ABCDEF" IDENTIFIED BY "123456"
   USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dc05zx21-scan1.internal.sas)
(PORT=1525))(CONNECT_DATA=(SERVICE_NAME=RXPRIORAXTHP)))';