?
Solved

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

Posted on 2014-03-14
6
Medium Priority
?
636 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:Praveen Kumar Chandrashekatr
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
Why Off-Site Backups Are The Only Way To Go

You are probably backing up your data—but how and where? Ransomware is on the rise and there are variants that specifically target backups. Read on to discover why off-site is the way to go.

 

Author Comment

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

Expert Comment

by:Praveen Kumar Chandrashekatr
ID: 39937604
0
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 2000 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

A publishing tool, a Version Control System, or a Collaboration Platform! These can be some of the defining words for the two very famous web-hosting Git repositories: Bitbucket and Github. Git is widely used amongst the programmers and developers f…
It’s a strangely common occurrence that when you send someone their login details for a system, they can’t get in. This article will help you understand why it happens, and what you can do about it.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

752 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