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!