Menu

Dipesh Majumdar

Blog and Paintings

Database Link

June 29, 2012

Introduction:

Let us first try to understand what database link means.

Supposing we have a user (test) in database orcl within host server testsrv. Now test user can access all objects residing in the same schema, i.e. test. Also test can access objects residing in another user (say test1) in the same database (or SID), by the help of dot operator.

But what if the Database changes? How to access objects of a different database? Also what if the host server is a remote one? Dot operator will no more help now. 

Therein lies the importance of database link. As the name suggests it is actually a link to objects residing in other databases. 

Given Scenario:

So for example the current user is test. Hostname is testsrv. SID is orcl. 

Requirement:

Access table emp_info residing in user test1, hostname- testsrv1, SID - orcl1. For this we have to create a database link in test user at testsrv. 

Solution: 

SQL Statement to create Oracle Database Link:

CREATE database link test1_dblink CONNECT TO test1 IDENTIFIED BY test1 USING '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = testsrv1)(PORT = 1521)) (CONNECT_DATA = (SID = orcl1) ) )'; 

Now we can access table emp_info belonging to test1 user residing in database orcl1 inside server testsrv1 from test user in database orcl inside server testsrv by using the below command:

select * from test1.emp_info@test1_dblink; 

Note: If we feel that 'test1.emp_info@test1_dblink' is too big a nomenclature, then we can use a synonym. Click here to know about synonyms.

create synonym emp_info for test1.emp_info@test1_dblink;

Now we can access the same data in a simpler way as shown below:

select * from emp_info; 

To conclude, a database link is a pointer in the local database that lets you access objects on a remote database.

<p><script async src="//pagead2.googlesyndication.com/pagead/js/adsbygoogle.js"></script>
<!-- Responsive1 -->
<ins class="adsbygoogle"
     style="display:block"
     data-ad-client="ca-pub-4292234158327342"
     data-ad-slot="4299610709"
     data-ad-format="auto"></ins>
<script>
(adsbygoogle = window.adsbygoogle || []).push({});
</script></p>

Go Back

Comment