Creating Database Link (aka DBLINK) in Oracle

I had to mess with Oracle database links recently. Here's a good reference to the "CREATE DATABASE LINK" sentence syntax.

In my case it was:

CREATE PUBLIC DATABASE LINK MYLINK CONNECT TO USER IDENTIFIED BY PASSWORD USING 'mydb';

'mydb' has to be an entry in ORACLE_HOME/network/admin/tnsnames.ora file on the server side Oracle client installation, so that the host database can see the foreign one (which is 'mydb').

Next, make sure that sqlnet is configured to use tnsnames.ora file. Its configuration is defined ORACLE_HOME/network/admin/sqlnet.ora, which contains client side network configuration parameters (again, on the server side!). For instance:

NAMES.DIRECTORY_PATH=(TNSNAMES, LDAP)

Next, we could test this database link:

SELECT * FROM dual@mydb;

Now it makes sense to use a synonym to point to the foreign table.

CREATE SYNONYM MYSYNONYM FOR dual@mydb;

Now the initial query will look as follows:

SELECT * FROM MYSYNONYM;