Overview of Oracle's topology solution
|
Oracle SQL*Net software provides the
ability to define all of the components of a distributed
network, including all database servers, clients, listeners and
database links. This global definition is called a “topology”.
To support the topology, SQL*Net, later re-named Net8, allows
Oracle database to connect with each other in a seamless
fashion. Connectivity is first established in Oracle by
defining "service names" in a special file.
The service name is translated by Oracle
to get all of the information it needs to connect to the remote
database. The next step is to create "database links" that
take the service name and add a remote USER ID and password.
Once defined by the DBA, these remote databases can participate
in queries and updates from within any Oracle application. For
example, a database in London and Paris can be defined to the
Denver system with the following SQL extension:
CREATE PUBLIC DATABASE
LINK london
CONNECT TO user_id
INEDTIFIED BY secret_password
USING 'london_unix';
CREATE PUBLIC DATABASE
LINK paris
CONNECT TO user_id
identified by secret_password
USING 'paris_vms';
Note that the USING clause of the
database link specifies a TNS service name. This service name
will be looked-up in the tnsnames.ora file to get the protocol,
IP address and the database system ID (SID) name.
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 Paris, and a ORDERLINE table in
London.
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;
Here we see that the SQL query joins
three tables, the local ORDER table and two remote tables. The
remote table CUSTOMER will access the customer table at London,
and the ORDER table in Paris will also be included.
You can also hide the fact that the
CUSTOMER and ORDER tables are remote by creating a synonym that
hides the database link name:
Create public synonym
customer for customer@london;
Create public synonym
order for order@paris;
The SQL could now be written as if the
tables were local to the database. |
