Network Topology   «Prev  Next»

Lesson 9 Overview of Oracle's topology solution
ObjectiveHow do I join tables from remote sites in a SQL query?

Inter Database Communication

Join Oracle Tables from Remote Sites

We can now include any tables from these remote sites by qualifying their remote site name in the SQL query.
This example joins three tables:
  1. a local ORDER table in Denver,
  2. a CUSTOMER table in London, and
  3. a ORDERLINE table in Paris.
 customer.cust_number = order.customer_number
 order.order_number = orderline.order_number;

Create Synonym for Remote Tables

The local ORDER table will access the CUSTOMER table at London, and the ORDERLINE table in Paris will also be included.
You can also hide the fact that the CUSTOMER and ORDERLINE tables are remote by creating a synonym that hides the database link name:

Create public synonym customer for customer@london;
Create public synonym orderline for orderline@paris;

The SQL could now be written as if the tables were local to the database.
The next lesson takes a look at the transparent network substrate (TNS).

Overview Toplogy - Exercise

Before moving on to the next lesson, click the Exercise link below to practice defining database links.
Overview Toplogy - Exercise