Interview Questions

Oracle Create DB Link

In this section lets see about how to access DB objects from one Database to another database.

Assume that you have two databases testlocalServ and testremoteServ. Log-in in to the database testlocalServ. If you want to access the tables from testremoteServ database, first of all you need to create a DB link to that remote database testremoteServ. OK, How to create a DB link? its very simple using the following DDL statement.

CREATE PUBLIC DATABASE LINK remote_conn CONNECT TO remote_user IDENTIFIED BY remote_pwd USING testremoteServ ;  

We have actually created a DB link remote_conn for the DB testremoteServ on the user remote_user.

In general the DDL query goes like this

CREATE [PUBLIC] DATABASE LINK <link_name> CONNECT TO <user_name> IDENTIFIED BY <password> USING '<service_name>';

Now you can access a table say , REMOTE_USERS present in the testremoteServ DB using the following query

SELECT * FROM REMOTE_USERS@remote_conn

One issue is you need to specify '@DB Link' name to access every remote DB object. Hm... don't worry there is an alternative to this issue by creating one such synonym as follows.

CREATE PUBLIC SYNONYM REMOTE_USERS FOR REMOTE_USERS@remote_conn

Once you have created the above synonym you can access the remote table using the following query.

SELECT * FROM REMOTE_USERS

You need to create synonyms for all the remote DB objects if you want to avoid using '@DBLink' clause.