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 [email protected];A synonym could be created to hide the remote link entirely:
CREATE SYNONYM employee FOR [email protected];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
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.