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.

• Creating Database Links
• Basic Usage
• General Management
• Common Mistakes
• Performance

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. 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 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.

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.

• 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.

• 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.

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

  The "tnsnames.ora" entry might look like this.

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

• 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.

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.

• 100% Practical Training

• Hands
  On
  Training

• 100%
  Job
  Gurantee

Interested to be a trainer?

jobs@greenstech.in

For corporate Training

corp@greenstech.in

Copyright © 2018 Greens Technologys. All Rights Reserved. Privacy Policy