Create Database Link in Another Schema

Creating database link has several restrictions. One of them is that you can’t create DB link in another schema, for example the following script is not correct:

create database link hr.remote_db_link
connect to k identified by k
using 'test'


If you run this then database link will be created with the name “hr.remote_db_link”  in your schema not in HR schema.

To solve this, there can be done the following:

–Create procedure from our user in HR which will execute database creation script

 create procedure hr."db_create_link_proc"
 is
 begin
      execute immediate  'create database link remote_db_link
                          connect to k identified by k
                          using ''test'' ';
 end;

–Execute that procedure from our schema

 begin
      hr."db_create_link_proc";
 end;

–Then you can drop this procedure from our schema

drop procedure hr."db_create_link_proc"

That is all.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: