| Overview of Oracle's topology solution
|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
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
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:
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.
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
Create public synonym orderline for
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