sql statements joining tables across multiple servers

Posted on 2007-10-15
Last Modified: 2013-12-19

I need to write sql statements joining tables across multiple servers. What are the options in Oracle 9i to accomplish this task?

Question by:GoodName
    LVL 22

    Accepted Solution

    You can create a link as:
    create database link otherdb connect to user identified by password using 'OTHERSID';

    You need to establish the link as a user on the other instance that has read access to the tables you need to join. Then:
    SELECT a.columns, ..., b.columns
      FROM yourTable a INNER JOIN otherdb.otherTable ON a.column = b.column
     WHERE ...
    ORDER BY ...;

    After the link is establiched, you can treat it as one of your tables in the selects, just append the link name to the front of the table name.


    LVL 28

    Assisted Solution

    also you can create a synonym in the local db as shown below as use it in your joins,

    create synonym s1 for a1@my_db_link;

    select a.*,b.*
    from emp a, s1 b
    where a.empno = b.empno;


    Author Comment

    Thank you, guys for your valuable advice. I'll like to go this direction.

    But my client feels somehow resistant against establishment of db link (link between servers), perhaps because of security or performance potential issues.

    What kind of issues might occur if to use this option?
    And would you please suggest what I can do to eliminate or at least minimize any negative effect on linked servers if there are any?

    LVL 22

    Assisted Solution

    Just creat a user (for the link) that has limited access. They can control the rights assigned to that user.


    Author Comment

    Hi guys,

    I tried to create a db_link but when tested it I got the following error message:

    ORA-12154: TNS:could not resolve service name

    Please advice.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
    I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
    Via a live example, show how to take different types of Oracle backups using RMAN.
    Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now