Oracle Tips  by Burleson Consulting

The Data Warehouse Development Life Cycle

SQL*Net For Oracle Distributed Data Warehouses 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. As mentioned earlier, 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 employeeLONDON;

This request will give you an error message that reads: ORA-02019: connection description for remote database not found. This message is received because of the way 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

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.