[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 928
  • Last Modified:

sql statements joining tables across multiple servers

Hi,

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

Thanks.
0
GoodName
Asked:
GoodName
  • 2
  • 2
3 Solutions
 
JimBrandleyCommented:
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.

Jim

0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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;

Thanks
0
 
GoodNameAuthor Commented:
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?

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

Jim
0
 
GoodNameAuthor Commented:
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.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now