Solved

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

Posted on 2014-03-14
6
547 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 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Foolproof security solutions has become one of the key necessities of every e-commerce or Internet banking website. If you too own an online shopping site then its vital for you to equip your web portal with customer security features that can allow…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

831 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