Connecting between Oracle databases

This tutorial shows how to create a database link from the Obiwan database to the Quigon database allowing you to link a set of distributed tables containing some sample data.

Part 1 – Creating the tables

Run SQL Plus and connect to Obiwan and type the following commands to create a table and populate it:


CREATE TABLE OBIPETS(ID NUMBER, NAME VARCHAR2(20)); INSERT INTO OBIPETS VALUES (1, 'Cat'); INSERT INTO OBIPETS VALUES (2, 'Dog'); COMMIT;

Next, open a new SQL Plus window and connect to the Quigon database.

Type the following commands to create and populate a table there:

CREATE TABLE QUIPETS(ID NUMBER, NAME VARCHAR2(20)); INSERT INTO QUIPETS VALUES (3, 'Budgie'); INSERT INTO QUIPETS VALUES (4, 'Goldfish'); COMMIT;

Part 2 – The database link from Obiwan to Quigon

Return to the Obiwan window and type the following commands to create a database link:

CREATE DATABASE LINK QUILINK CONNECT TO userid IDENTIFIED BY password USING 'quigon';

Remember to substitute your userid and password for the Quigon database in the above example.

e.g. if your userid was aa111 and the password was anygoodpass you would type the following:

CREATE DATABASE LINK QUILINK CONNECT TO AA111 IDENTIFIED BY anygoodpass USING 'quigon';

Next, create a synonym on Obiwan for the table on Quigon.

CREATE SYNONYM PETSATQUI FOR QUIPETS@QUILINK;

Create a view combining the two tables:

CREATE VIEW ALLPETS AS (SELECT * FROM OBIPETS UNION SELECT * FROM PETSATQUI);

Once you’ve created this view, you can check to see if it retrieves information from both databases with the following command:

SELECT * FROM ALLPETS;

Note that Oracle does little in the way of checking when you create synonyms or database links, so if you make a mistake you may need to drop them and go back and change them.

To do this, type the following:

DROP DATABASE LINK QUILINK; DROP SYNONYM PETSATQUI;

Comments are closed.