0

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.

MT0
  • 143,790
  • 11
  • 59
  • 117
RAHUL SONI
  • 59
  • 1
  • 7
  • 1
    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 Sep 12 '22 at 10:06

1 Answers1

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:

  1. Grant privilege create database link to target schema.
  2. Create a stored procedure in your target schema, which creates database link per execute immediate
  3. Call this procedure
  4. Finally drop this procedure.
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
RobertG
  • 416
  • 1
  • 8