Summary: Materialized View across DB link eating up too many system resources Question: Is there a way to throttle a materialized view refresh?
We have some fairly large tables that we copy from one oracle DB to several others as a cached backup if the network to the central database is down (or the central database is down). The data copy runs hourly or daily (depending on the data). It's not mission critical or real-time.
Our first idea was materialized views on the receiving databases to pull the data from the central database across a database link. If I remember right we couldn't do incremental refreshes because of the database links so the refreshes are full table refreshes.
We discovered when the materialized view refreshes run they eat up lots of system resources sometimes overloading the system and slowing critical stuff. (I'm a programmer not DBA so I'm not entirely sure what resource was constrained, but I think disk access, possibly memory.)
Is there any way to throttle a materialized view refresh so it won't do that?