create a database link without changing the TNSNAMES.ORA

The basic syntax of creating a public database link is:

CREATE PUBLIC DATABASE LINK NAME.OF.DATABASE.LINK
CONNECT TO schema_name
IDENTIFIED BY "schema_password"
USING 'tnsnames entry';

However, many times even if you have the database permission to create a db link (CREATE DATABASE LINK system privilege), you have no access to the TNSNAMES.ORA file on the server.

Without adding the entry to the TNSNAMES.ORA file any attempt to select from this database link will result in a ORA-12154: TNS:could not resolve the connect identifier specified error.
The simple solution is to create a database link without changing the TNSNAMES.ORA file. The syntax is:

CREATE PUBLIC DATABASE LINK NAME.OF.DATABASE.LINK

CONNECT TO schema_name IDENTIFIED BY " schema_password"

USING

'(DESCRIPTION=

(ADDRESS=

(PROTOCOL=TCP)

(HOST=hostname-or-IP)

(PORT=1521))

(CONNECT_DATA=

(SID=sid-name)))'

;

Since Oracle 10, it is possible to use the abbreviated version

CREATE DATABASE LINK NAME.OF.DATABASE.LINK
CONNECT TO schema_name IDENTIFIED BY schema_password
USING '//hostname-or-IP:1521/sid';

It is important to note that while this trick might be great as temporary workaround, it is recommended to use the old fashion TNSNAMES method to create a constant database link. This is especially true in a multiple servers environment where a databases can be copied from one environment to another (prod to test for example). In such cases, the copied database might include links to prod machines while the tnsnames.ora file will still point to the correct machine.

Leave a Reply

Your email address will not be published. Required fields are marked *