We need to create a Reporting DB to use for Reporting and a Transactional DB will be in separate Host. Basically we need to pull data from a set of tables from Transactional DB and move to Reporting DB schema at proper intervals.
We might have to get data from different column from different tables and store it in a single table/multiple tables in Reporting DB.
Solution (Please validate):
I am planning to use Oracle Materialized views in the same host as Transactional DB to achieve the following part of requirment
"We might have to get data from different column from different tables and store it in a single table/multiple tables in Reporting DB."
and then to use Oracle streams to move it in scheduled interval to Reporting DB.
You can use materialized views. But take into consideration that for scheduling MV's, you'll have to use maetrailized view logs on the source tables to capture net change. And for MV logs you'll need to consider constraints on the source tables.