23

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.

João V.
  • 61
  • 1
  • 12
Arunkumar
  • 279
  • 1
  • 3
  • 7

8 Answers8

16

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

anandchaugule
  • 901
  • 12
  • 20
  • 1
    "+1" it was the solution to fix my problem. I was setting the USING clause to a service name that exist in tnsnames.ora but for some reason is not beign mapped. So I set USING clause to the whole connection descriptor. – Sandoval0992 Jun 20 '19 at 21:18
10

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

daywalker_ora
  • 101
  • 1
  • 3
  • 1
    Two questions. 1) The link provided indicates that where you have `_username_` should be the schema you want to link to, and where `_passwd_` is, should be the table name in that schema to use. Which is correct? 2) Assuming your example is correct, where do you specify the database to connect to? (I've looked at that Oracle page and it's clear as mud) – JMichael Jun 18 '15 at 16:25
  • Agreed @JMichael. The oracle example is: In the following statement, user hr on the remote database defines a fixed-user database link named local to the hr schema on the local database: CREATE DATABASE LINK local CONNECT TO hr IDENTIFIED BY hr USING 'local'; – MinneapolisCoder9 Jan 27 '20 at 15:24
3

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);
ChandraBhan Singh
  • 2,841
  • 1
  • 22
  • 27
3

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>';
  1. 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

pavankumar2028
  • 43
  • 4
1

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

ARGStackOvaFlo
  • 185
  • 1
  • 4
  • 16
1
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:

vampirepapi
  • 11
  • 3
0

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.

Talha Ansar
  • 1
  • 1
0

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)))';
Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
user21900165
  • 1