Data refresh from one oracle db to another oracle db

Question

I want to get some filtered data from one oracle db and refresh tables in other oracle db and this refresh needs to be done frequently. So what are best possible ways to do it?

Please suggest the optimal way to do it. Using db links or using oracle schedule jobs or write java code.

Answer 1

There are numerous ways to do this, but the most straightforward is to use materialized views with queries that involve dblinks, which you can schedule refreshes for by using dbms_scheduler. There are a lot of docs online to help you. Here's one:

Working with Materialized Views

Answer 2

I don't know Java so I can't comment it.

As far as database is concerned, one option is to create database link between these two databases and a materialized view in one of them which fetches data over the database link from another database.

You can schedule refresh; there are various options. Read documentation to pick the right one for your situation. Have a quick look at Tim Hall's materialized views article; if you find it interesting, search Oracle documentation (related to version you use) for more info.

Answer 3

create a database link between source and target databases and follow any of these native tool options.

1. Create a materialized view using query that points to source database.
2. Write a procedure in target site using select queries to read data from source site and update/insert the target tables accordingly.Later schedule those procedures using scheduler jobs.
3. Use the Golden gate provided if table you chosen should have primary key or unique key.
4. you can write your own Java or python code which works like PUB and SUB mode to publish the data into target site.