Oracle creating Database link

Question

I am using Oracle 11g express edition. I have created tables, stored procedures and it works fine. I have my user "System" with password "xyz" (main user during installation).

Then i have created two databases "abc" and "pqr" with same user.

I wanted to create database link from abc to pqr.

create database link testlink
connect to pqr identified by xyz
 using '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))   (CONNECT_DATA=(sid=xe)))';

I am getting error "Insufficient privileges". Please help me out.

Answer 1

It should be CONNECT TO username not the database name as shown in the following image which describes the syntax of CREATE DATABASE LINK. We define database instance/service under USING connect_string clause.

Prerequisites

To create a private database link, you must have the CREATE DATABASE LINK system privilege. To create a public database link, you must have the CREATE PUBLIC DATABASE LINK system privilege. Also, you must have the CREATE SESSION system privilege on the remote Oracle database.

Reference:CREATE DATABASE LINK

Demo

[oracle@orcl Desktop]$ sqlplus system/oracle

SQL> create user abc identified by abc;

User created.

SQL> create user xyz identified by xyz;

User created.

SQL> grant create session to abc;

Grant succeeded.

SQL> conn abc/abc
Connected.

SQL> create database link testlink connect to pqr identified by pqr using '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orcl.dba.com)(PORT=1522))   (CONNECT_DATA=(service=orcl)))';
create database link testlink connect to pqr identified by pqr using '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orcl.dba.com)(PORT=1522))   (CONNECT_DATA=(service=orcl)))'
                     *
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> conn system/oracle
Connected.
SQL> grant create database link to abc;

Grant succeeded.

SQL> create database link testlink connect to pqr identified by pqr using '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orcl.dba.com)(PORT=1522))   (CONNECT_DATA=(service=orcl)))';^[[3~^C

SQL> conn abc/abc
Connected.
SQL> create database link testlink connect to pqr identified by pqr using '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orcl.dba.com)(PORT=1522))   (CONNECT_DATA=(service=orcl)))';

Database link created.