oracle-databasedblinksysdba

Oracle - unable to create DB link

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.

Solution

  • 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.