How to refresh materialized view in oracle
Run this script to refresh data in materialized view:
BEGINDBMS_SNAPSHOT.REFRESH('Name here');END;
try this:
DBMS_SNAPSHOT.REFRESH( 'v_materialized_foo_tbl','f');
first parameter is name of mat_view
and second defines type of refresh
. f denotes fast refresh.but keep this thing in mind it will override any any other refresh timing options.
a bit late to the game, but I found a way to make the original syntax in this question work (I'm on Oracle 11g)
** first switch to schema of your MV **
EXECUTE DBMS_MVIEW.REFRESH(LIST=>'MV_MY_VIEW');
alternatively you can add some options:
EXECUTE DBMS_MVIEW.REFRESH(LIST=>'MV_MY_VIEW',PARALLELISM=>4);
this actually works for me, and adding parallelism option sped my execution about 2.5 times.
More info here: How to Refresh a Materialized View in Parallel