Oracle Syntax for Creating Database Link Owned by Another User

Question

The typical syntax for creating a db link is as follows:

create database link remote_db_link 
connect to remote_user 
identified by remote_password 
using 'remote_db'

But I'd like my DB link owned by another account after it's created. Is there a way to do this?

The following does NOT work:

create database link anotheruser.remote_db_link 
connect to remote_user 
identified by remote_password 
using 'remote_db'

The "selected" answer says "you cannot create a database link in another user's schema". This is not exactly true. There IS "Oracle syntax" that can accomplish this. See my answer. — spencer7593, Jun 20 '12 at 19:09

spencer7593 · Answer 1 · 2011-09-19T22:44:46.697

Sathya is correct, in that the CREATE DATABASE LINK syntax does not allow creating a database link in another schema. HOWEVER...

WORKAROUND

It IS possible to create a database link in another user's schema, as long as anotheruser has CREATE DATABASE LINK privilege, and the user you are connected as has CREATE ANY PROCEDURE privilege.

Here's the workaround I use:

    create procedure anotheruser."tmp_doit_200906121431"
    is
    begin
      execute immediate '
        create database link remote_db_link 
        connect to remote_user 
        identified by remote_password 
        using ''remote_db'' ';
    end;
    /
    begin
      anotheruser."tmp_doit_200906121431";
    end;
    /
    drop procedure anotheruser."tmp_doit_200906121431"
    /

Let's unwind that. First, I create a procedure in the anotherusers's schema; this procedure contains the CREATE DATABASE LINK statement that I want to run.

When the procedure is executed, it runs as the owner of the procedure, such that the CREATE DATABASE LINK statement is executed by anotheruser.

The name of the procedure is not important, except that I need to make sure that it doesn't conflict with any existing object name. I use lowercase letters (enclosing the procedure name in double quotes), using "tmp" to mark this object as "temporary", and using the current yyyymmddhh24miss as the part of the procedure name. (I usually run a query of DBA_OBJECTS to check that a matching object_name does not exist.)

For a "one-off" type admin function, this is a viable workaround. I prefer this to the other alternative: saving the anotheruser's password, changing the password, connecting as the user, and resetting anotheruser's password back to the saved.)

Sathyajith Bhat · Accepted Answer · 2014-05-20T06:38:23.583

9

Restrictions on DBLinks - You cannot create a database link in another user's schema, and you cannot qualify dblink with the name of a schema.

edited May 20 '14 at 06:38

answered Jun 12 '09 at 18:33

Sathyajith Bhat

21,321
22
95
134

:-( I was afraid of that. Thanks all for your time. – Jeff Jun 12 '09 at 18:36
1

It is *not* accurate to say "*you cannot create a database link in another user's schema*". This is not true. The CREATE DATABASE LINK syntax doesn't allow for it, but it *is* possible. See my answer. – spencer7593 Mar 10 '20 at 15:51

score -3 · Answer 3 · answered May 30 '12 at 17:45

-3

AS a sys user you can view all db links in SYS.DBA_DB_LINKS view. That view use link$ and user$ table. You can create new dblink as usually and it show at link$ table. Then change owner (use id from user$). commit. Done.

answered May 30 '12 at 17:45

Polu

1

Thank you for answering but this question has an already accepted answer. – alestanis Oct 21 '12 at 11:57
2

Note that the approach described in this answer is *not* supported by Oracle. Oracle strenuously warns against issuing DML to modify the contents of tables in the SYS schema, except under direction of Oracle support. – spencer7593 Aug 22 '14 at 01:04

Oracle Syntax for Creating Database Link Owned by Another User

3 Answers3