Oracle - unable to create DB link

Question

I am unable to create the DB link as it's throwing ORA - 01031 insufficient privileges error. Let's say I have database DB1 and schema name as s1 and second database as DB2 with schema t1. I am trying to create the DB link by sysdba user by running below -

alter session set current_schema=s1;
Create database_link dblinkname connect to t1 identified by password using DB2;

But this is giving me error. I tried giving privileges also to s1 but no luck. Any leads. I don't have the schema password for s1 and I can't reset it as it's production environment.

DB links must be created by the owner of the link. You cannot create a database link directly in another schema, even as SYSDBA. You must login as s1 or work around things as suggested by RobertG. — pmdba
– pmdba, Commented Sep 12, 2022 at 10:06

marc_s · Accepted Answer · 2022-10-16 06:45:39Z

1

I had the same issue a several years ago.

Assuming, you don't want to create a public database link...

You can do this:

Grant privilege create database link to target schema.
Create a stored procedure in your target schema, which creates database link per execute immediate
Call this procedure
Finally drop this procedure.

edited Oct 16, 2022 at 6:45

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Sep 12, 2022 at 7:10

RobertG

4163 silver badges8 bronze badges

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

Collectives™ on Stack Overflow

Oracle - unable to create DB link

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related