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.

  • +1 for a good first question! p.s. welcome to the forum! :-) – Vérace Apr 14 '19 at 14:58


  1. list - Comma-delimited list of materialized views that you want to refresh. ... Alternatively, you may pass in a PL/SQL index-by table of type DBMS_UTILITY.UNCL_ARRAY, where each element is the name of a materialized view.

  2. atomic_refresh - If this parameter is set to true, then the list of materialized views is refreshed in a single transaction. All of the refreshed materialized views are updated to a single point in time. If the refresh fails for any of the materialized views, none of the materialized views are updated.

  3. Maybe flashback query. Using Oracle Flashback Query DBMS_FLASHBACK.ENABLE_AT_TIME.

| improve this answer | |
  • Does MVIEW referesh locks the source tables till they are completely refereshed?. If that is so then using it in OLTP system will cause issues . – Vishesh Apr 17 '19 at 13:53
  • @Vishesh Tables need not to be locked to have a consistent state of them, even if that is from the past and not the present. That is what you have undo for. – Balazs Papp Apr 17 '19 at 18:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.