How to enable automatic out-of-place MV refresh?
I have a large MV, defined with "force" method, automatic refresh every 5 minutes:
CREATE MATERIALIZED VIEW MV_TEST REFRESH FORCE START WITH SYSDATE NEXT SYSDATE + 5/1440 AS . . .
How do I alter this MV to enable out-of-place option for future automatic refreshes?
This MV refresh takes only a few seconds to run, since it is defined as "force" and meets all requirements for "fast" refresh. But, when we need to rebuild or modify a master table (every couple years), the MV refresh runs in "complete" mode, very slowly. Last week when we modified one of this MV's master tables, the MV refresh ran for 30 hours, hitting (or causing) ORA-1555, then proceeded to roll back for another 46 hours before releasing the lock on the MV.