Querying remote schema using dblink

I created a dblink pointing to a remote schema. How do I query the remote schema using this dblink?

    Requires Free Membership to View

    Login

Here's an example.

Create the link using a fixed user:
CREATE DATABASE LINK homeoffice.ourcompany.com
CONNECT TO scott IDENTIFIED BY tiger
USING 'homeoffice';
To query the employee table on the remote database, you'd query as follows:
SELECT *
  FROM employee@homeoffice.ourcompany.com;
A synonym could be created to hide the remote link entirely:
CREATE SYNONYM employee
   FOR scott.employee@homeoffice.ourcompany.com;
Then you could query like this:
SELECT *
  FROM employee ;
The synonym would resolve the remote reference. The key is to remember that the @ will denote a remote database.

For More Information


This was first published in June 2002

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top