How do I access data from multiple databases in oracle

sam_sark
sam_sark used Ask the Experts™
on
Hi
I want to implement SELECT, UPDATE and DELETE SQL statement that access data from multiple dateases. This datebases may be in the same server or remote locations.

For example
1)customer table may be in one location and product table in another loaction. I want to create a SQL that is
SELECT custmer_name, phone_no
FROM Customer, Product
WHERE order_id = product_id and product_nm="DUNLOP TIRE"
 


2)Need to update table Patient_info thats reside in two different locations server in different databases.

3) Should I be able to implement join queries in multiple databease?

If someone can help me out that would be great. Thanks in advance.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
You can access the table from the multiple database.

You hace to create a DB link using the tns names.

create database link <dblink name> connect to <username > identified by <password > using '<remote db tns>'

One the db link is created, create a sysnonym for the remote tables in the local database.

In the local db,

create synonym <syn name> for <username>.<table_name>@<dblink_name>;

The synonym name can be same as the remote table name as long as there is no other table with the same name in the local schema.

Hope this helps,
P.S.Vel.

Commented:
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
Accept psvel's comment as answer
Please leave any comments here within the next seven days.
 
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
 
jpkemp
EE Cleanup Volunteer

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial