8i | 9i | 10g | 11g | 12c | 13c | 18c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Here

Managing Database Links

I am not a big fan of database links, mostly because of the way people abuse them. This article discusses some of the general points about database links, as well as highlighting the common mistakes associated with their use.

Related articles.

Creating Database Links

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.

-- 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';

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 USING clause points to an entry in the database servers "tnsnames.ora" file. For local links, the special entry of 'local' is used.

Basic Usage

Once a database link is created, for example the "scott_remote" link, remote objects can be referenced as follows.

SELECT * FROM emp@scott_remote;

INSERT INTO emp@scott_remote
  (employee_id, last_name, email, hire_date, job_id)
VALUES
  (999, 'Claus', 'sclaus@example.com', 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);

Synonyms can be used to hide the database link. This can make things look neater, but may hide the complexity of an operation.

CREATE SYNONYM emp FOR emp@scott_remote;

General Management

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.

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;

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 possible, always avoid public database links.

Common Mistakes

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.

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.

Performance

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.

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

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:

Hope this helps. Regards Tim...

Back to the Top.