Solved

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

Posted on 2014-03-14
6
520 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
6 Comments
 
LVL 142

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 34

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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 34

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

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

Join & Write a Comment

Suggested Solutions

Introduction HyperText Transfer Protocol (http://www.ietf.org/rfc/rfc2616.txt) or "HTTP" is the underpinning of internet communication.  As a teacher of web development I have heard many questions, mostly from my younger students who have come to t…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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.

706 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

18 Experts available now in Live!

Get 1:1 Help Now