Distributed Queries

Oracle's distributed database management system architecture lets you access data in remote databases using Oracle Net and an Oracle server. You can identify a remote table, view, or materialized view by appending @dblink to the end of its name. The dblink must be a complete or partial name for a database link to the database containing the remote table, view, or materialized view.

See Also: "Referring to Objects in Remote Databases" for more information on referring to database links Oracle9i Net Services Administrator's Guide for information on accessing remote databases

Restrictions on Distributed Queries

Distributed queries are currently subject to the restriction that all tables locked by a FOR UPDATE clause and all tables with LONG columns selected by the query must be located on the same database. For example, the following statement will raise an error:

SELECT employees_ny.* 
    FROM employees_ny@ny, departments 
    WHERE employees_ny.department_id = departments.department_id
    AND departments.department_name = 'ACCOUNTING' 
    FOR UPDATE OF employees_ny.salary; 

The following statement fails because it selects long_column, a LONG value, from the employees_review table on the ny database and locks the employees table on the local database:

SELECT employees.employee_id, review.long_column, employees.salary 
    FROM employees, employees_review@ny review 
    WHERE employees.employee_id = employees_review.employee_id
    FOR UPDATE OF employees.salary;

In addition, Oracle currently does not support distributed queries that select user-defined types or object REFs on remote tables.