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?

1 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.
  • Do I need to change tnsname.ora I didn't get the documentation ??
    – kinkajou
    Mar 30, 2012 at 4:14
  • I modified the answer. Hope that is clear. Mar 30, 2012 at 13:09
  • Thank you for the answer we do this on server side or client side? I have modified tnsnames.ora on client already. I have been using sql server so it's quite confusing for getting started with oracle.
    – kinkajou
    Mar 31, 2012 at 4:03
  • 2
    All the changes are on the server side except for the last create database link step that can be done from any connection. Apr 2, 2012 at 13:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.