I have a requirement where I have to Load data periodically from more than 100 tables of an OLTP oracle database into MS SQL database. In my solution I want to use MVIEWs of these tables with Fast Refresh as the point of data extraction in oracle.
Every time before triggering the data load into MS SQL database I will refresh the MVIEWs to get the data. However have below mentioned doubts:
1) Is there a way through which i can refresh all the MVIEWs at once?
2) How can I ensure that data changes happening during the MVIEW refresh will not be reflected in the refreshed MVIEW. e.g. I started a refresh of all the MVIEWs at 10:00 AM then how can I ensure that no changes happening after 10:00 AM reflects in the refreshed MVIEWs?
3) Is there a better way of achieving this? I don't have the option of using mirror database.