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'

Answer 1

Um, that's because you're trying to create an object (dblink) in someone else's schema. Why not just create it as a PUBLIC database link?

Answer 2

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.

Answer 3

Sathya is correct, the syntax does not allow you to create a database link in another schema. Let's assume that you do have privilege to create a procedure in another schema, there is a workaround:

    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"
    /

(This allows the "create database link" statement to be executed as the owner of the procedure.)

Answer 4

Spencer that is a beautiful trick, I have to face this problem everytime I need to create a dblink for another user, a beautiful and creative solution.

Tks for sharing.