Oracle : Create database link on another schema

Even as an administrator for the database, it sometime possible that we don't have the password of the schema. Most of the time we can just log in as SYS and then create the objects for the particular schema.

For example we can create a table for a particular schema by just added the owner name before the table name.

Create table AAA.TEST (A CHAR);

The table will be create under the ownership of the schema AAA.

However this is not possible for database link. As the syntax for creating database link does not take owner into consideration.

If you would create a database link using

Create database link AAA.TESTLNK ....

You will end up with a database link "AAA.TESTLINK" owner by SYS.

So the trick now is to create a procedure under the schema that create the database link. This way the database link will be create under the ownership of the schema and not SYS. Script as below :


create or replace procedure AAA.CREATEDBLINK
is
begin
execute immediate 'create database link TESTLINK
connect to BBB identified by "Password"
using ''REMOTEINSTANCE''';
end;
/

Execute the procedure AAA.CREATEDBLINK to create the database link

exec AAA.CREATEDBLINK

Subsequently drop the procdure.

drop procedure AAA.CREATEDBLINK;


Original source : http://forums.oracle.com/forums/thread.jspa?threadID=507523