I agree to TechTarget’s Terms of Use, Privacy Policy, and the transfer of my information to the United States for processing to provide me with relevant information as described in our Privacy Policy.

Please check the box if you want to proceed.

I agree to my information being processed by TechTarget and its Partners to contact me via phone, email, or other means regarding information relevant to my professional interests. I may unsubscribe at any time.

Please check the box if you want to proceed.

By submitting my Email address I confirm that I have read and accepted the Terms of Use and Declaration of Consent.

link?

A database link creates a connection between a local database and a remote database. Read on to learn how to create an Oracle database link, as well as how to troubleshoot any potential errors.

There are a number of reasons you may want to create database links. For example, you might want to link an Oracle database to a non-Oracle database, or you might want the data in a remote database to update when the local database updates. Here's how to do those things and more.

Creating an Oracle database link

First, create a database link which points to the other location. The database link can be created with a command similar to the following:

You'll want to give the database link a better name. Use the appropriate user ID and password to connect to the remote database, and configure your TNSNAMES.ORA file with a TNS alias to point to that database. You can test the database link with a simple query, such as the following:

SELECT sysdate FROM dual@other_db;

The @other_db clause uses the database link you created. If the query returns the date, then the link works properly. Once the link is set up, you can either issue a command to modify the data in the remote database when you modify the local database or you can use a database trigger. For instance, if the application modifies the local database by performing an INSERT similar to the following:

INSERT INTO my_table VALUES (1,'Two');

Then you can also code another SQL statement to insert the same values across a database link.

Database links from remote databases

You can also use a database link to pull data from a remote database to a local one. To pull data through a database link, you must create a database link to the remote database. You can construct SQL commands to insert data into the local database, selecting from the remote database. A SQL statement might look like the following:

The SQL language in these situations is a very powerful and flexible tool. But you must write your own routines to move the data.

Database links can also be used for partial database replication. Oracle Replication Services performs this, but the additional costs may not be justifiable when replicating a small subset of your database. If that is the case, you can write your own replication routines using database triggers and links. Here's how to create the database link to the remote database:

Database links for migrations

You can use a database link to migrate a database or copy data from one database to another that has the same structure. To migrate from Oracle9i to Oracle 10g, create an Oracle 10g database on your target server and create a database link from the Oracle 10g database to the Oracle9i database. Run create table as select statements to create your tables with the data in the Oracle 10g database. Remember to also create any required indexes, constraints and referential integrity.

In addition, you can create database links between an Oracle database and a non-Oracle database, such as Microsoft SQL Server or IBM DB2. For this, use Oracle Heterogeneous Services.

If you run into errors after creating your database link, note that you must correctly configure your TNSNAMES.ORA file or you will get the ORA-12154 error (TNS: could not resolve service name). One thing that people don't understand about database links is that they will only look in $ORACLE_HOME/network/admin for a TNSNAMES.ORA file, and this file must reside on the server that the database is running. Be sure to correctly configure this file in this location for your database links. Always test database links by issuing the following query:

The ORA-12505 error, meanwhile, simply means that the ORACLE_SID in your TNS alias does not match any ORACLE_SID defined for the database listener. You'll have to make sure your TNS alias SID matches the one defined in the listener's LISTENER.ORA configuration file.

To troubleshoot the ORA-02068 error, the database link requires a TNS alias, which you specified in the USING clause of the CREATE DATABASE LINK command. Outside of Oracle, check to be sure you can use this TNS alias to connect to the database. Using SQL*Plus, see if you can connect to the following remote database:

sqlplus system/password@tns_alias

My guess is that the above will give you the same error. This means either the TNS alias is defined incorrectly, or that the instance is not really running. If you can connect with SQL*Plus, the database link should work just fine.

Join the conversation

4 comments

Register

I agree to TechTarget’s Terms of Use, Privacy Policy, and the transfer of my information to the United States for processing to provide me with relevant information as described in our Privacy Policy.

Please check the box if you want to proceed.

I agree to my information being processed by TechTarget and its Partners to contact me via phone, email, or other means regarding information relevant to my professional interests. I may unsubscribe at any time.