Database Link

Database link allows us to connect from one schema (where we are making database link) to the other schema in the other database instance. For this example I will be connection from user tester to schema newschema in the database SID: DEVELOPMENT2

create database link link_name connect to username identified by password using ‘ipadress:port/SID’;

As we can not make database link in another user’s schema, we have to log in as that user and create database link (we could also make public database link instead, but I want here to make it only for that schema), for which we need following privilege.

SQL> grant create database link to tester;

Now we connect as user tester and make the database link
SQL> connect tester/password;
tester@DEVELOPMENT1> create database link dblink_newschema
     connect to newschema identified by newpassword
     using '10.100.100.10:1521/DEVELOPMENT2.oracle.com';

(using should have full service name that is stated in tnsnames.ora file, in place of DEVELOPMENT2, 
for example development2.oracle.com ip and port have to be written regardless of the full service name)

Database link is successfully created and now we will connect to the newschema through db link. 

tester@DEVELOPMENT1> select count(*) from newschema.table_name@dblink_newschema; 

Name of the DB link will depend on the fact, whether the parameter global_names is true or false

SQL> show parameter global_n

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
global_names                         boolean     TRUE

If it is false, you can name db link as you wish, but if it is true, 
you have to name it like the global name of the database to which you are making db link

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
DEVELOPMENT.ORACLE.COM

So the db link should be created as

tester@DEVELOPMENT1> create database link DEVELOPMENT.ORACLE.COM
     connect to newschema identified by newpassword
     using '10.100.100.10:1521/DEVELOPMENT2.oracle.com;
(using should have full service name that is stated in tnsnames.ora file, in place of DEVELOPMENT2, 
for example development2.oracle.com ip and port have to be written regardless of the full service name)
 Database link is successfully created and now we will connect to the newschema through db link. 
tester@DEVELOPMENT1> select count(*) from newschema.table_name@DEVELOPMENT.ORACLE.COM; 
Of course the question arises, what can we do, when we want more than one db link on the same instance. 
We can use @some_name at the end of the db link name. 
tester@DEVELOPMENT1> create database link DEVELOPMENT.ORACLE.COM@newschema 
connect to newschema identified by newpassword 
using '10.100.100.10:1521/DEVELOPMENT2.oracle.com';  

and using it: 
select count(*) from newschema.table_name@DEVELOPMENT.ORACLE.COM@newschema;   
tester@DEVELOPMENT1> create database link DEVELOPMENT.ORACLE.COM@otherschema 
connect to otherschema identified by otherpassword 
using '10.100.100.10:1521/DEVELOPMENT2'; 

and using it: 
select count(*) from otherschema.table_name@DEVELOPMENT.ORACLE.COM@otherschema; 

Now we will revoke the create database link privilege as admin user 
SQL> revoke create database link from tester;
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: