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

Inter Database Communication

Joining Tables from Remote Sites Using SQL Queries in Oracle

Oracle databases support distributed queries, which enable you to join tables located in remote sites using SQL queries. This is achieved through the use of database links, which provide a connection between local and remote Oracle databases. In this article, we will discuss the process of creating a database link and using it to join tables from remote sites in an SQL query.

Creating a Database Link: To join tables from remote sites, you first need to create a database link. A database link is a schema object that enables a connection between two Oracle databases. The general syntax for creating a database link is as follows:
```
CREATE [SHARED] [PUBLIC] DATABASE LINK link_name
  CONNECT TO username IDENTIFIED BY password
  [USING 'connect_string'];
```
For example, to create a database link named "remote_db_link" connecting to a remote Oracle database with the username "user_remote" and password "password_remote", the SQL statement would be:
```
CREATE DATABASE LINK remote_db_link
  CONNECT TO user_remote IDENTIFIED BY password_remote
  USING 'connect_string';
```
Replace 'connect_string' with the appropriate connection string for the remote database, which can be an Oracle Net connect descriptor, a TNS alias, or an Easy Connect string.
Joining Tables from Remote Sites: Once the database link is created, you can join tables from remote sites by referencing the table along with the database link in the SQL query. The general syntax for querying a remote table using a database link is:
```
table_name@database_link
```
Suppose you have two tables, "local_table" in your local database and "remote_table" in the remote database, with a common column named "id". To join these tables using an SQL query, the statement would be:
```
SELECT l.column1, r.column2
FROM local_table l, remote_table@remote_db_link r
WHERE l.id = r.id;
```
This query retrieves "column1" from the "local_table" and "column2" from the "remote_table", joining them on the "id" column.
In conclusion, joining tables from remote sites in Oracle databases can be achieved using SQL queries through the creation and utilization of database links. By establishing a connection between the local and remote databases, you can seamlessly join tables across sites and execute distributed queries.

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:

a local ORDER table in Denver,
a CUSTOMER table in London, and
a ORDERLINE table in Paris.

SELECT 
   customer.customer_name, 
   order.order_date, 
   orderline.quantity_ordered
FROM 
   customer@london, 
   order, 
   orderline@paris
WHERE
 customer.cust_number = order.customer_number
 AND
 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