Database Link Syntax

It is tempting to create database links by refencing the aliases from a tnsnames.ora file. e.g.

CREATE DATABASE LINK SCOTTS_DB_LINK
CONNECT TO SCOTT IDENTIFIED BY tiger
USING 'PROD_SERV';

But the "one ring to rule them all" tnsnames.ora approach can come back and bite you if the tnsnames.ora file is ever accidentally lost or - worse - replaced with one that is pointing, say, to Production, rather than Development databases!

Rather than using tnsnames.ora aliases, hard-code instead. e.g.

CREATE DATABASE LINK SCOTTS_DB_LINK
CONNECT TO SCOTT IDENTIFIED BY tiger
USING '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=PROD01.my.domain)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=PROD_SERVICE))))';

Now, no matter what happens to your tnsnames.ora file, you know that your database link is pointing to the correct database.