A database link allows you to reference objects in a remote database. Typically, the remote database will be another Oracle database, but it can be any ODBC compliant database (see Heterogeneous Services - Generic Connectivity ). When accessing a remote object over a database link, the local database is acting as an Oracle client. There are a variety of syntax variations in the documentation , but the ones you will most commonly use are as follows. Private database links are only visible to the owner of the link. Public database links are visible to all users in the database and are as such a potential security nightmare. Most commonly, a database link is used to connect to a user in a remote database, where the clause points to an entry in the database servers "tnsnames.ora" file. For local links, the special entry of 'local' is used.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

-- Remote Username: scott
-- Remote Password: tiger

-- Private database link to a user in a remote database.
CREATE DATABASE LINK scott_remote 
   CONNECT TO scott IDENTIFIED BY tiger
   USING 'remote_database';

-- Private database link to a user in a remote database, with full connection string.
CREATE DATABASE LINK scott_remote 
   CONNECT TO scott IDENTIFIED BY tiger
   USING '(DESCRIPTION=
            (ADDRESS=(PROTOCOL=TCP)(HOST=server1.example.com)(PORT=1521))
            (CONNECT_DATA=(SERVICE_NAME=HRDEV1))
          )';

-- Public Database link.
CREATE PUBLIC DATABASE LINK scott_remote 
   CONNECT TO scott IDENTIFIED BY tiger
   USING 'remote_database';

-- Private database link to a user in the local database.
CREATE DATABASE LINK scott_local 
   CONNECT TO scott IDENTIFIED BY tiger
   USING 'local';

Once a database link is created, for example the "scott_remote" link, remote objects can be referenced as follows. Synonyms can be used to hide the database link. This can make things look neater, but may hide the complexity of an operation.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

SELECT * FROM emp@scott_remote;

INSERT INTO emp@scott_remote
  (employee_id, last_name, email, hire_date, job_id)
VALUES
  (999, 'Claus', '[email protected]', SYSDATE, 'SH_CLERK');

UPDATE emp@scott_remote
SET    salary = sal * 1.1
WHERE  job    = 'SALES';

DELETE FROM emp@scott_remote
WHERE  empno = 7788;

EXEC delete_emp@scott_remote(p_empno => 7788);

CREATE SYNONYM emp FOR emp@scott_remote;

You can't create database links for another user, even from a DBA user. Instead you have to log into the user that owns them, directly or via a proxy user . Maybe something like this. This will sometimes scare people away from private database links and instead they will use public database links. This causes a couple of problems: - If you ever want to consolidate the database, you have to consider the impact of public resources, assuming you are not using the multitenant option. - Everybody, and I really mean everybody, who connects to the database has access to the public database link. That is a massive security issue. If possible, always avoid public database links.

1
2
3
4
5
6
7
8
9
10
11

CONN my_dba_user@my_service
ALTER USER scott GRANT CONNECT THROUGH my_dba_user;
GRANT CREATE DATABASE LINK TO scott;

CONN my_dba_user[scott]@my_service

DROP DATABASE LINK my_db_link;
CREATE DATABASE LINK my_db_link CONNECT TO scott IDENTIFIED BY tiger USING 'remote_db';

CONN my_dba_user@my_service
REVOKE CREATE DATABASE LINK FROM scott;

The biggest problem with database links is not the fault of database links themselves, but how they are abused. In my opinion, the most common mistakes are as follows. - Link Names : It's really annoying to have links named after databases that no longer exist or systems that were replaced years ago, but you can't change them without revisiting a bunch of code. Use a general service-based name, rather than a link named after the product or the database it connects to. For example, a link to get staff information from a HR database might be called hr_staff, not "ORCL" after the database. Link Names : It's really annoying to have links named after databases that no longer exist or systems that were replaced years ago, but you can't change them without revisiting a bunch of code. Use a general service-based name, rather than a link named after the product or the database it connects to. For example, a link to get staff information from a HR database might be called hr_staff, not "ORCL" after the database. - Link Users : A database link connects to a database user. Each database link should have its own user in the remote system, which should not be used by anyone else. If you don't follow this rule, someone will change a password for a user, which breaks a bunch of database links that point to it. Reusing an existing user is just plain lazy and will lead to problems down the line. Absolutely never have a database link to a schema owner! Instead, create a new user with access to the necessary objects, and create the database link pointing to the new user. Link Users : A database link connects to a database user. Each database link should have its own user in the remote system, which should not be used by anyone else. If you don't follow this rule, someone will change a password for a user, which breaks a bunch of database links that point to it. Reusing an existing user is just plain lazy and will lead to problems down the line. Absolutely never have a database link to a schema owner! Instead, create a new user with access to the necessary objects, and create the database link pointing to the new user. - USING Clause : Use a service-specific "tnsnames.ora" entry in the definition of the database link, not a database-specific alias. When you clone between production, dev and test, having a different alias in each DB link means you have to recreate them after each clone. A consistent approach saves you lots of effort. Rather than recreating the DB link, simply make sure the service-specific alias in the "tnsnames.ora" file is pointing to correct location. The "tnsnames.ora" entry might look like this. USING Clause : Use a service-specific "tnsnames.ora" entry in the definition of the database link, not a database-specific alias. When you clone between production, dev and test, having a different alias in each DB link means you have to recreate them after each clone. A consistent approach saves you lots of effort. Rather than recreating the DB link, simply make sure the service-specific alias in the "tnsnames.ora" file is pointing to correct location. The "tnsnames.ora" entry might look like this. - Link Usernames and Passwords : The database link users should have the same username and password in each environment, once again making cloning easy. These passwords are not used by anyone but the DB link, so I think it is acceptable to be consistent between all environments. Once again, it means there is no recreation of DB links after a cloning operation. The password aging should be disabled for the link users. The fact the link users are only used by the database links means password aging is a problem waiting to happen, so don't allow it. Link Usernames and Passwords : The database link users should have the same username and password in each environment, once again making cloning easy. These passwords are not used by anyone but the DB link, so I think it is acceptable to be consistent between all environments. Once again, it means there is no recreation of DB links after a cloning operation. The password aging should be disabled for the link users. The fact the link users are only used by the database links means password aging is a problem waiting to happen, so don't allow it. If you've followed these guidelines, provided the tnsnames.ora entries are pointing to the correct place, cloning a production system to refresh a test system will work without change as far as the DB links are concerned.

1
2
3
4
5
6
7
8
9
10
11

-- Rather than this.
CREATE DATABASE LINK hr_staff CONNECT TO hr_staff_link IDENTIFIED BY Password1 USING 'HRDEV1';

-- Use something link this.
CREATE DATABASE LINK hr_staff CONNECT TO hr_staff_link IDENTIFIED BY Password1 USING 'hr_staff_service';

hr_staff_service=
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=TCP)(HOST=server1.example.com)(PORT=1521))
    (CONNECT_DATA=(SERVICE_NAME=HRDEV1))
  )

As you might expect, pulling data across a network takes time. By default, all joins are processed on the side of the link issuing the query, the local database. As a result, you might end up pulling lots of data over the network, only to throw it away during a join operation. The DRIVING_SITE hint allows you to specify which database should process the query, allowing you to reduce the amount of network traffic. In addition, references to remote objects may affect the optimizations available for a statement, so don't be surprised if you get an unexpected execution plan. For more information see: - Proxy User Authentication and Connect Through in Oracle Databases - Heterogeneous Services (Generic Connectivity) : Oracle to Non-Oracle Database Links Using ODBC - CREATE DATABASE LINK - DRIVING_SITE Hope this helps. Regards Tim...

1
2
3

SELECT /*+ DRIVING_SITE(emp) */ * 
FROM   emp@scott_remote
       JOIN dept on emp.deptno = dept.deptno;