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.

Did you mean created two users? Instead of databases? If so check that whether the user create database link has the CREATE DATABASE LINK system privilege and the connecting user has CREATE SESSION system privilege. — atokpas
– atokpas, Commented Jan 5, 2017 at 5:40

Community · Accepted Answer · 2020-06-20 09:12:55Z

7

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.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jan 5, 2017 at 5:30

atokpas

3,3571 gold badge13 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

poonam Over a year ago

yes when i try to execute grant create database link i get same error message. How Do I give system privilege?

atokpas Over a year ago

Connect as sys user with sysdba privilege then try to grant the required privileges. For example: sqlplus sys as sysdba then SQL> grant ...

poonam Over a year ago

Here main issue is i cannot login as sys user also. I tried sys and sys and other combinations too.

atokpas Over a year ago

@poonam: Then connect as SYSTEM user and grant CREATE DATABASE LINK to abc user. Next, connect as abc user and create the database link. Refer to the demo.

Brian Leach Over a year ago

Public database links are a security vulnerability. Use them with caution.

Collectives™ on Stack Overflow

Oracle creating Database link

1 Answer 1

Prerequisites

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Prerequisites

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related