Location Transparency with Oracle Oracle Tips by Burleson Consulting

The Data Warehouse Development Life Cycle

Distributed Oracle Data Warehouses

Implementing Location Transparency With Oracle

To clarify location transparency further, let’s consider the following example. Assume we have a parts inventory system with separate databases in London, Paris, Washington, and Albuquerque. The manager wishes to know the number of clocks on-hand in all of the locations and issues the following SQL command to the distributed database manager:

SELECT count(*) FROM PART WHERE partname = 'clock';

This example is called a global transaction, and the end user does not know or care what databases are interrogated to satisfy the request. In this example, it is the responsibility of the database to query all of the distributed INVENTORY tables, collect the counts from each table, and merge the responses into a single result set. Using Oracle SQL*Net, location transparency is achieved by creating database links to the remote database and then assigning a global synonym to the remote tables.

Database links are created with a link name that corresponds to a Transparent Network Substrate (TNS) name, which is declared in the USING clause of the database link statement. In the following example, the link name is london, which corresponds to a TNS name of london_host. The london_host string is then looked up in the tnsnames.ora file, and the host name, database name, and protocol are gathered.

SQL> CREATE PUBLIC DATABASE LINK london
CONNECT TO london_user IDENTIFIED BY secret_password
USING london_host;

---

This is an excerpt from "High Performance Data Warehousing", copyright 1997.

If you like Oracle tuning, you may enjoy the book Oracle Tuning: The Definitive Reference , with over 900 pages of BC's favorite tuning tips & scripts.  You can buy it directly from the publisher and save 30%, and get instant access to the code depot of Oracle tuning scripts.