Search the Site

Sponsors

bottom corner

Creating database links

If you have multiple Oracle databases, sometimes it may be useful to have them linked together for ease of query. The following note illustrates how to do so.

This page is filed under keyword(s): oracle.

The following example assumes you have two databases, db1 and db2. You are currently logged into db1 and need a link to db2. We also assume the entry for db2 has already been established in the server's TNSNAMES.ORA file.

-- Fixed user method
create database link db2
connect to scott
identified by tiger 
using 'db2';

-- Connected user method
create database link db2
using 'db2';

-- Current user method
create database link db2
connect to current_user 
using 'db2';

The difference between the three methods above is user authentication. In the fixed user example, regardless of who you are in db1, you will always access db2 as the user "scott". With the connected user method, the link will always connect to db2 as whoever created the link; naturally, with this method, you will need to make sure you have a valid account in db2 as well. With the current user method, the link will always connect to db2 as whoever is using the link; this is arguably the most secure method.

If you need the link to be seen by every user, you may consider a public link:

create public database link db2
using 'db2';

With the database link established, you are now able to perform DML queries in db2 from db1. Here's an example:

select a.name, b.telephone
from myschema.user_list a, scott.address_book@db2 b
where a.name=b.name;

Note the usage of "@db2" in the above example which signifies the usage of the database link.

One thing to keep in mind when establishing database links is security, thus, make sure you understand the potential risks of your design before implementing it. Fixed user method is the most commonly used one, and the fixed user typically ends up having more privileges in the remote database than necessary, therefore allowing users in db1 to do more things in db2 than necessary. Another concern is with how Oracle stores fixed user passwords -- they are stored in the SYS.LINK$ table in clear text!

Did you find this page useful? Please consider browsing other articles or subscribing to the RSS feed to keep up with latest.

This page is filed under keyword(s): oracle.
Author: C. Peter Chen
Last updated: 8 Apr 2008

bottom corner