Oracle PL/SQL: Creating A Database Link

This Knol is a quick developers guide to creating a link between two Oracle databases using TNS Information but not editing the TNSNames.ora file on the server.


It is quite often the case that it is better to link two databases together and report from one of them rather than draw together information from multiple data sources - usually because the link can be re-used more easily and as you have a requirement to re-use it then it stands to reason other people might.

The problem is that DBA's don't generally like developers making changes to the TNS file on the server - and usually this TNS file will only include information for the current database - not all the databases in your company.

Fortunately the CREATE DATABASE LINK command lets you either name a specific TNS entry OR specify the details. To create a database link called DBLINK_TEST to another oracle database which *does not* exist in the TNS file you can use;

CREATE DATABASE LINK DBLINK_TEST
       CONNECT TO
       IDENTIFIED BY
       USING '
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = 
      (PROTOCOL = TCP)
      (HOST = )
      (PORT = ))
           )
    (CONNECT_DATA = (sid = )))'

There is something to be said for not using the TNS file (especially in an Oracle EBS environment) as if you keep creating multiple clones of multiple systems the complexities of keeping the file up to date (and yes I know there are ways of automating it) might not be worth the time.