Tutorial: Querying Multiple Oracle Databases

A distributed query accesses information in two or more databases. In a synonym or in a SELECT statement, you can identify a remote table, view, or materialized view by appending @dblink to the end of its name. The dblink is a database link to the database that contains the remote database object.

Meet the following conditions before running the distributed query in this topic:

Satisfy the prerequisites described in "Preparing to Access and Modify Information in Multiple Oracle Databases".
Create a database link from the local database to any remote database that contains a database object involved in the query. In the example in this topic, the SYSTEM user at the ii1.example.com database uses a database link that connects to the SYSTEM user at the ii2.example.com database. See "Tutorial: Creating a Database Link" for information about creating such a database link.
Ensure that the hr sample schema is installed on the local database, and the oe sample schema is installed on the remote database. These sample schemas are installed by default with Oracle Database.

This topic uses ii1.example.com and ii2.example.com as sample databases. You can substitute any two databases in your environment that meet these conditions.

For this example, assume the following:

A company keeps its human resources information in the hr schema at the ii1.example.com database and its order entry information in the ii2.example.com database.
The employee_id in the hr.employees table corresponds with the sales_rep_id in the oe.orders table.
A manager has a question about an order and wants to contact the sales representative for the order.

In this case, the contact information for the sales representative is in the hr.employees table in the ii1.example.com database, and the order information is in the oe.orders table in the ii2.example.com database.

To run a distributed query that combines the information at the ii1.example.com and ii2.example.com databases to show the contact information for the sales representative:

Create a synonym for the remote database object. In this example, create a synonym called ord in the hr schema that points to the oe.orders table at the ii2.example.com database:
1. Log in to Enterprise Manager as SYSTEM user.
2. Go to the Database Home page for the ii1.example.com database instance.
3. Click Schema to open the Schema subpage.
4. Click Synonyms in the Database Objects section.
5. On the Synonyms page, click Create.
6. On the Create Synonym page, enter ord in the Name field in the General section.
7. With Schema selected for the Type, enter hr in the Schema field or click the flashlight icon to select the hr schema.
8. In the Database section, select Remote.
9. Click the flashlight icon for the Service Name field to select the ii2.example.com database link for the SYSTEM user.
10. In the As Alias For section, enter oe.orders.
11. Click OK to create the synonym.
Note: You can also use the CREATE SYNONYM SQL statement to create a synonym.
On a command line, open SQL*Plus and connect to the ii1.example.com database as the SYSTEM user.

See Starting SQL*Plus and Connecting to the Database for more information about starting SQL*Plus.

Run the following query:

COLUMN FIRST_NAME HEADING 'First Name' FORMAT A20
COLUMN LAST_NAME HEADING 'Last Name' FORMAT A20
COLUMN PHONE_NUMBER HEADING 'Phone Number' FORMAT A20

SELECT e.first_name, e.last_name, e.phone_number
  FROM hr.employees e, hr.ord o
  WHERE o.order_id    = 2456 AND
        e.employee_id = o.sales_rep_id;

The output will be similar to the following:

First Name           Last Name            Phone Number
-------------------- -------------------- --------------------
Danielle             Greene               011.44.1346.229268

Tutorial: Querying Multiple Oracle Databases

Related Topics