| Previous | Table of Contents | Next |
SQL*Net supports network transparency such that the network structure may be changed without affecting the SQL*Net application. Location transparency is achieved with database links and synonyms.
Let’s trace a sample data request through SQL*Net. Essentially, SQL*Net will look for the link name in the database link table (dba_db_links) and extract the service name. The service name is then located in the tnsnames.ora file, and the host name is extracted. Once again, we have a three-stage process beginning with the link name, referencing the service name, which references the host name.
In Unix environments, the host name is found in a host file (/etc/hosts), and the Internal Protocol (IP) address is gathered. In the following example, london_unix might translate into an IP address of 143.32.142.3. These four steps illustrate how SQL*Net takes a remote request and translates it into the IP address of the destination database:
SELECT * FROM CUSTOMER@LONDON
CREATE PUBLIC DATABASE LINK LONDON CONNECT TO london_unix_d;
london_unix_d = (description=(address=(protocol=tcp) (host=seagull) (port=1521) (connect_data=(sid=london_sid) (server=dedicated)))
143.32.142.3 london_sid london_unix.corporate.com
As you can see, this translation occurs in a multistage process. The tnsnames.ora file specifies the name of the host containing the destination database. For Unix environments, this host name is then looked up in the /etc/hosts file to get the IP address of the destination box.
Note that the service name is looked up in tnsnames.ora—if the service exists, the IP address is found in the /etc/hosts file and a communications request is sent to the destination IP address. Note that both of the entries in this file connect to London, but london_unix_d directs SQL*Net to spawn a dedicated process, while london_unix uses the multithreaded server component because a shared server is specified.
Now that you have the tnsnames.ora and /etc/hosts files in place, you can include any tables from the London sites by qualifying the remote site name in the SQL query:
SELECT customer.customer_name, order.order_date FROM customer@london, ORDER WHERE customer.cust_number = order.customer_number;
Note that this query joins two tables at different locations, and the database link called london determines how the Oracle connection will be established on the destination system. Regardless of how the connection is made to the destination, however, the user ID must have SELECT privileges against the customer table, or this query will fail.
Connections to remote databases can be made by specifying either service names or connect strings. Connect strings use the full connection. In the example below, the “t:” means a TCP/IP connection, “host:” is the name of the remote processor, and “database” is the name of the database on that processor:
emp@my_db
sqlplus /@t:host:database
Connect strings are stored in the dba_dblinks table, and are created with the CREATE DATABASE LINK command:
CREATE PUBLIC DATABASE LINK ny_emp FOR ny_emp@t:myhost:mydatabase
SQL*Net can establish database communications in three ways: via a remote connection, a remote request, or a distributed request. A remote connection is the easiest way to make a database connection. The sending database simply makes a request by specifying a table name suffixed by “@”. SQL*Net takes it from there, seamlessly accessing the remote database and returning the data to the initiating system. Communication is established simply by making a distributed request to a remote database. Within Oracle, @ specifies the remote database name, but the functionality of the @ operator depends on where it is used. Here’s an example:
sqlplus scott/tiger@london
SELECT COUNT(*) FROM EMPLOYEE;
COUNT(*)
------------
162
In this request, Scott is using SQL*Plus to connect to the London database, and @london is the service name, as defined in the tnsnames.ora file. SQL*Net recognizes this as a remote connection and determines the appropriate linkage to establish communications with London. Internally, Oracle will check the tnsnames.ora file to ensure that london is a valid destination.
Now, observe another way of connecting to London from the same database. This is called a remote request:
sqlplus scott/tiger
SELECT COUNT(*) FROM employee@london;
COUNT(*)
--------------
162
Unlike a remote connection made directly from SQL*Plus, this remote request has Scott connecting to the local copy of SQL*Plus to specify the remote table (in this case, employee@london). In order for a remote request to work, a database link must define london. A database link is a connection pathway to a remote database that specifies the service name of the remote database. Without the database link, the following request would fail:
sqlplus scott/tiger SELECT COUNT(*) FROM EMPLOYEE@LONDON;
This request will give you an error message that reads, “ORA-02019: connection description for remote database not found.” The reason for this message is the way that Oracle defines the @ operator. When entering an Oracle service such as SQL*Plus, the @ operator will go directly to the tnsnames.ora file to manage the request, while the @ operator from within an Oracle program specifies the use of a database link.
To make the code functional, you must define a database link that specifies the service name used to establish the connection. Note that the database link name and the service name are the same in this example, but the database link and the connect descriptor are not related in any way:
CREATE DATABASE LINK LONDON USING 'london';
SELECT COUNT(*) FROM employee@london;
COUNT(*)
--------------
162
| Previous | Table of Contents | Next |