Database Link Example

Let’s assume we have two databases, NEW (the local database) and LEGACY (the remote database). On the LEGACY database, we create a simple table with a unique index and some data:

CREATE TABLE t_legacy (
    anId NUMBER(10) NOT NULL,
    aValue VARCHAR2(100)
);
CREATE UNIQUE INDEX t_legacy_index ON t_legacy(anId);
INSERT INTO t_legacy VALUES(1, 'Test data');
INSERT INTO t_legacy VALUES(2, 'Test data 2');
INSERT INTO t_legacy VALUES(3, 'More testdata');

On the NEW database, we now create a database link to the remote database:

CREATE DATABASE LINK LegacyLink
CONNECT TO scott IDENTIFIED BY tiger
USING 'Legacy';

Now we can access the data in the LEGACY database, while connected to the NEW database:

SELECT *
FROM t_legacy@LegacyLink;

1 Test data
2 Test data 2
3 More testdata

We can also use other DML statements like INSERT, UPDATE and DELETE to manipulate the table in the remote database. Note that database constraints still apply to the distributed system as a whole. For example, transactions are distributed across the local and the remote database. Open two sessions, one on the LEGACY database and one on the NEW database and execute the following statements in the shown order:

Session on NEW database:	Session on LEGACY database:
INSERT INTO T_LEGACY@LegacyLink VALUES (4, 'Additional data'); 1 row created.
	INSERT INTO t_legacy VALUES (4, 'Additional data'); <BLOCKED>
COMMIT;
	ORA-00001: unique constraint (LEGACY.T_LEGACY_INDEX) violated