How to create oracle linked server in oracle server itself?

Question

Is it possible to create oracle linked server in other oracle server? I actually wanted to do this with sql server but in sql server I couldn't run long query (8000 limit).

I have access to remote database but only read only

I can't create views and stored procedure.

I want to create linked server and with it's help create views in my local oracle database. Is this feasible and is the speed of query execution comparable?

Answer 1

The concept you are looking for on the Oracle side is a Database Link. See this introductory information in the Concepts Guide. Then follow the link at the end of the section to the Administrators Guide where you can learn more about it. You will also want to look at the Database Gateway documentation for the OS you are on, specifically the section on Configuring Oracle Database Gateway for ODBC which will explain steps need to prepare the system for non-Oracle Database Links.

To answer your concerns...

• Read only access is fine as long as you don't want to change the remote database.
• Not being able to create remote views or stored procedures won't be a problem. If you create Materialized Views then a remote Materialized View Log may be useful, but not necessary.
• Using database links on the Oracle side you can create views of remote tables or even join local data with remote data. Yes, the speed is comparable. It won't be as fast since the data has to flow over the network, and sometimes the query plan won't be ideal, but these may not be issues depending on your situation.

Here are the steps you will need to perform:

1. Create a system DSN.
2. Create an appropriate ORACLE_HOME\hs\admin\ .ora file.
3. Add an appropriate ORACLE_HOME\network\admin\listener.ora entry.
4. Add an appropriate ORACLE\network\admin\tnsnames.ora entry.
5. Reload the listener.
6. Create the database link.