Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer 1

up vote 4 down vote accepted

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.
share|improve this answer
    
Do I need to change tnsname.ora I didn't get the documentation ?? –  kinkajou Mar 30 '12 at 4:14
    
I modified the answer. Hope that is clear. –  Leigh Riffel Mar 30 '12 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 '12 at 4:03
1  
All the changes are on the server side except for the last create database link step that can be done from any connection. –  Leigh Riffel Apr 2 '12 at 13:06
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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