Nasty buggers,
Just came across a mview that didn’t gave results when it should’ve.
Most likely, it was in the wrong refresh_group, below the query to check in which refresh_group the mview is:
SELECT r.rowner as Owner , r.rname as Refresh_group , r.job as JOb_id , c.name as Mview , r.next_date next_refresh , r.broken FROM DBA_REFRESH r , DBA_REFRESH_CHILDREN c WHERE r.job = c.job /
With thanks to Agis Stamatopoulos
When the Mview needs to be moved from 1 refresh_group to another:
DBMS_REFRESH – Procedure SUBTRACT
execute DBMS_REFRESH.SUBTRACT( name => 'my_group_1' , list => 'mv_market_rate');
DBMS_REFRESH – Procedure ADD
execute DBMS_REFRESH.ADD( name => 'my_group_1' , list => 'mv_borrowing_rate');
With thanks to Database Journal