Manage Learn to apply best practices and optimize your operations.

How to create an Oracle database link

From remote databases to database migrations, this tip shows you how to create an Oracle database link and avoid potential pitfalls.

Database links can be difficult to navigate, never mind create. So how do you go about creating an Oracle database...

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:

CREATE DATABASE LINK other_db CONNECT TO scott IDENTIFIED BY tiger USING 'tns_alias';

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.

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

Additionally, you can create a trigger.

CREATE TRIGGER modify_remote_my_table BEFORE INSERT ON my_table BEGIN INSERT INTO my_table@other_db VALUES(:new.colA, :new.colB); END; /

You can write similar triggers to update and delete rows, as well.

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:

INSERT INTO my_local_table (ColA, ColB, ColC) SELECT ColA, ColB, ColC FROM remote_table@remotedb WHERE conditions are true;

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:

  1. CREATE DATABASE LINK link_name CONNECT TO username IDENTIFIED BY password USING 'tns_alias';
  2. Create a trigger on the table that populates newly inserted rows into the remote database table.
  3. CREATE TRIGGER my_table_insert_trig BEFORE INSERT ON my_table BEGIN INSERT INTO my_table@link_name VALUES (:new.colA, :new.colB, ..., :new.colX); END; /

You'll have to create your own triggers similar to those above that can perform the database replication for you.

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:

SELECT sysdate FROM dual@remotedb;

If you get the results back, the link is working fine. Otherwise, fix the corresponding error.

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. 

Next Steps

How do I do that in Oracle?

Master select query on dblink

Speed up imports on database links

This was last published in April 2017

Dig Deeper on Oracle DBA tools

Join the conversation

4 comments

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

What are the most common errors you've encountered with Oracle database links?
Cancel
can you show dblink avi so that one can under stand more clearly
Cancel
Do you know how to get the oracle dblink configured to access a remote db2 database version 6.1, I have my oracle 10g installed in linux.
Thanks
Cancel
A good article from the Oracle point-of-view. What would be really helpful is the other side of the coin such as dblinks from say SQL Server to Oracle OBIEE RPD layer
Cancel

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close