|Oracle® Database Administrator's Guide
11g Release 2 (11.2)
Part Number E17120-05
The database allows the following standard DML statements to reference remote tables:
SELECT...FOR UPDATE (not always supported in Heterogeneous Systems)
Queries including joins, aggregates, subqueries, and
SELECT...FOR UPDATE can reference any number of local and remote tables and views. For example, the following query joins information from two remote tables:
SELECT e.empno, e.ename, d.dname FROM firstname.lastname@example.org e, email@example.com d WHERE e.deptno = d.deptno;
In a homogeneous environment,
LOCK TABLE statements can reference both local and remote tables. No programming is necessary to update remote data. For example, the following statement inserts new rows into the remote table
emp in the
scott.sales schema by selecting rows from the
emp table in the
jward schema in the local database:
INSERT INTO firstname.lastname@example.org SELECT * FROM jward.emp;
Restrictions for Statement Transparency:
Data manipulation language statements that update objects on a remote non-Oracle Database system cannot reference any objects on the local Oracle Database. For example, a statement such as the following will cause an error to be raised:
INSERT INTO remote_table@link as SELECT * FROM local_table;
The database does not allow remote DDL statements (for example,
DROP) in homogeneous systems except through remote execution of procedures of the
DBMS_SQL package, as in this example:
DBMS_SQL.PARSE@link_name(crs, 'drop table emp', v7);
Note that in Heterogeneous Systems, a pass-through facility lets you execute DDL.
LIST CHAINED ROWS clause of an
ANALYZE statement cannot reference remote tables.
In a distributed database system, the database always evaluates environmentally-dependent SQL functions such as
USERENV with respect to the local server, no matter where the statement (or portion of a statement) executes.
Note:Oracle Database supports the
USERENVfunction for queries only.
A number of performance restrictions relate to access of remote objects:
Remote views do not have statistical data.
Queries on partitioned tables may not be optimized.
No more than 20 indexes are considered for a remote table.
No more than 20 columns are used for a composite index.
There is a restriction in the Oracle Database implementation of distributed read consistency that can cause one node to be in the past with respect to another node. In accordance with read consistency, a query may end up retrieving consistent, but out-of-date data. See "Managing Read Consistency" to learn how to manage this problem.
See Also:Oracle Database PL/SQL Packages and Types Reference for more information about the