Solved

Oracle update table with the value from another database in different network

Posted on 2014-03-14
6
581 Views
Last Modified: 2014-05-08
Basically I am using oracle9i,Where I have one database say db1,Now I have one more database in different network say db2,both are connected using LAN. Now using oracle,I want to update db2 table values from db1,I heard it can be done automatically,Oracle has some feature.Please help me asap.
0
Comment
Question by:Sudhanshum
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39928792
0
 
LVL 12

Expert Comment

by:praveencpk
ID: 39933856
it its a same tables and you want to keep both the database in sync, you can use oracle Streams or Golden Gate for using these you need oracle licence.

http://docs.oracle.com/cd/B10500_01/server.920/a96571.pdf

http://www.oracle.com/technetwork/articles/havawala-goldengate-091741.html
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 39934900
"...using oracle, I want to update db2 table values from db1, I heard it can be done automatically".  The real world may not be quite as simple as what you described.

Yes, Oracle supports creating a "database link" that allows two different Oracle databases (instances) on two different servers (or on the same physical server) to communicate.  Oracle database links support select, insert, update and/or delete statements plus running PL\SQL procedures.  They do not support DDL statements (create, drop , alter, etc.)  

But no, "database links" are not created nor used automatically.  They must be manually created, and you must first add an entry in the tnsnames.ora file on the local server that points to the remote server.  Then you can create the database link.  Note that database links work from one side only .  That is, if you create the link on db1, that allows it to select, insert, update or delete data from db2 (depending on the privileges in db2 of the username that you use for the link).  But, this link will *NOT* allow db2 to access anything in db1.  If yo uwant access in that direction, you need to create the opposite link on the db2 server (again, after you add an entry to the tnsnames.ora file on server db2 pointing to the db1 server and database).

And, after the link(s) is/are in place you have to manually configure PL\SQL objects and/or materialized views, etc., to use the link(s) and you can schedule those.  Then, some things can happen automatically using the links.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Sudhanshum
ID: 39936156
Please give me article for showing DB Link creation and use asap.
0
 
LVL 12

Expert Comment

by:praveencpk
ID: 39937604
0
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 500 total points
ID: 39937618
Be aware that Oracle database links can be very helpful.  But, they are not always fast!  The performance of SQL statements executed over a DB link can be 10x, 100x, 1000x or more times slower than similar statements executed within a single Oracle database.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Using Quotation Marks in PHP This question (http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28217211.html) seems to come up a lot for developers who are new to PHP.  And it got me thinking, "How can we explain the rule…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to recover a database from a user managed backup

749 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