Q
Problem solve Get help with specific problems with your technologies, process and projects.

Querying remote schema using dblink

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

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


Dig Deeper on Oracle hosting, outsourcing and remote DBAs

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close