Part of our "How do I do that in Oracle?" series, this tip, compiled largely of expert advice from our Oracle database design and architecture expert Brian Peasland, explains in detail how to create a database link in Oracle, as well as how to troubleshoot errors with your db link.
A database link creates a connection between a local database and a remote database. You might want to create a database link, for instance, if you want the data in a remote database updated when the local database is updated. Here's how to accomplish this:
The first thing you need to do is to create a database link pointing 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 userid/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 like 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 for updating and deleting rows as well. (From "Updating data in remote database when local database is updated")
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 need to create a database link to the remote database. You should be able to 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 that can't be beat. But you'll have to write your own routines to move the data. (From "Getting data from remote 8i database to 9i)
Database links can also be used for partial database replication. Oracle has a product called Replication Services which performs this type of replication, but this product costs additional funds which may not be justifiable for replicating a small subset of your database. If that is the case, then you can write your own replication routines using database triggers and database links.
- Create the database link to the remote database:
CREATE DATABASE LINK link_name CONNECT TO
username IDENTIFIED BY password USING 'tns_alias';
- Create a trigger on the table which populates newly inserted rows into the remote database table.
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 the above which perform your replication for you. (From "Partial database replication setup")
You can use a database link to migrate a database or copy data from one database to another with the same structure. For instance, to migrate from Oracle 9i to Oracle 10g: Create an Oracle 10g database on your target server and create a database link from the Oracle 10g database to the Oracle 9i database. Run 'create table as select …' (CTAS) statements to create your tables with the data in the Oracle 10g database. Remember to also create any required indexes, constraints and referential integrity. (From "Migrating 9i database on Windows to 10g on SLES)
In addition, you can create database links between an Oracle database and a non-Oracle database such as SQL Server or DB2. For this use Oracle Heterogeneous Services (formerly known as Oracle Transparent Gateways).
If you run into errors after creating your database link, read the following advice.
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 it 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! So 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, then the link is working fine. Otherwise, fix the corresponding error. (From "Copying tables from one DB to another via database link")
The ORA-12505 error, on the other hand, 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. (From "DB link only works in one direction")
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 ensure that you can use this TNS alias to connect to the database. Using SQL*Plus, see if you can connect to this remote database:
sqlplus system/password@tns_alias
My guess is that the above will give you the same error. This means that either the TNS alias is defined incorrectly, or the instance is not really running. If you can connect with SQL*Plus, the database link should work just fine.
(From "ORA-02068 with database link")
Reader feedback
Jim Amos writes: "Prove that a database link goes to the exact database you want by one of the two queries below. These queries are a more specific test than selecting sysdate from dual@dblink_name.
SELECT * from global_name@dblink_name;
Or this one:
SELECT instance_name FROM V$INSTANCE@dblink_name;
Return to "How do I do that in Oracle?"
