Using an Oracle Database Link for Remote Queries

If you are a user in the LOCAL database shown in Figure 1, you can access objects in the REMOTE database via a database link. To do this, simply append the database link name to the name of any table or view that is accessible to the remote account. When appending the database link name to a table or view name, you must precede the database link name with an @ sign.

For local tables, you reference the table name in the FROM clause:

For remote tables, use a database link named REMOTE_CONNECT. In the FROM clause, reference the table name followed by @REMOTE_CONNECT:

NOTE

If your database initialization parameters include GLOBAL_ NAMES=TRUE, then the database link name must be the same as the name of the remote instance you are connecting to.

When the database link in the preceding query is used, Oracle will log into the database specified by the database link, using the username and password provided by the link. It then queries the BOOKSHELF table in that account and returns the data to the user who initiated the query. This is shown graphically in Figure 2. The REMOTE_CONNECT database link shown in Figure 2 is located in the LOCAL database.

As shown in Figure 2, logging into the LOCAL database and using the REMOTE_ CONNECT database link in the FROM clause returns the same results as logging in directly to the remote database and executing the query without the database link. It makes the remote database seem local.

NOTE

The maximum number of database links that can be used in a single session is set via the OPEN_LINKS parameter in the database’s initialization parameter file.

Queries executed using database links do have some restrictions. You should avoid using database links in queries that use the CONNECT BY, START WITH, and PRIOR keywords. Some queries using these keywords will work (for example, if PRIOR is not used outside of the CONNECT BY clause and START WITH does not use a subquery), but most tree-structured queries will fail when using database links.