Database links can be difficult to navigate, never mind create. So how do you go about creating an Oracle database...
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:
- CREATE DATABASE LINK link_name CONNECT TO username IDENTIFIED BY password USING 'tns_alias';
- Create a trigger on the table that 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 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:
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.
How do I do that in Oracle?
Master select query on dblink
Speed up imports on database links