• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 694
  • Last Modified:

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

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
Sudhanshum
Asked:
Sudhanshum
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
Praveen Kumar ChandrashekatrDatabase Analysist Senior Commented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
"...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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
SudhanshumAuthor Commented:
Please give me article for showing DB Link creation and use asap.
0
 
Praveen Kumar ChandrashekatrDatabase Analysist Senior Commented:
0
 
Mark GeerlingsDatabase AdministratorCommented:
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

Independent Software Vendors: 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!

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