Oracle syntax for linking to a database owned by another user

Question

A 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 would like my link to the database to belong to another account after it was created. Is there any 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

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

Answer 2

Satya is correct because the syntax does not allow you to create a link to a database in another schema. BUT... CREATE DATABASE LINK

Temporary solution

IS , anotheruser CREATE DATABASE LINK, , , CREATE ANY PROCEDURE.

:

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

. -, anotherusers; CREATE DATABASE LINK, .

, , CREATE DATABASE LINK anotheruser.

, , , - . ( ), "tmp", "", yyyymmddhh24miss . ( DBA_OBJECTS, , _ .)

"" . : , , .)

Answer 3

As the sys user, you can view all db links in the SYS.DBA_DB_LINKS view. In this view, use the $ link and the user table $. You can create a new dblink, as usual, and show it in the $ link table. Then change the owner (use the identifier from user $). to commit. Done.