How to refresh materialized view in oracle

Question

Iam trying to refresh the materialized view by using:

DBMS_MVIEW.REFRESH('v_materialized_foo_tbl')

But it's throwing invalid sql statement.

Then I have created a stored procedure like this:

CREATE OR REPLACE 
PROCEDURE MAT_VIEW_FOO_TBL 
IS
BEGIN
   DBMS_MVIEW.REFRESH('v_materialized_foo_tbl')
END MAT_VIEW_FOO_TBL IS;

This procedure has been created successfully but when i am calling this procedure with

MAT_VIEW_FOO_TBL;

it's throwing an error again.

Kindly suggest a solution for this issue.

Thanks, Srinivas

Answer 1

Best option is to use the '?' argument for the method. This way DBMS_MVIEW will choose the best way to refresh, so it'll do the fastest refresh it can for you. , and won't fail if you try something like method=>'f' when you actually need a complete refresh. :-)

from the SQL*Plus prompt:

EXEC DBMS_MVIEW.REFRESH('my_schema.my_mview', method => '?');

Answer 2

You can refresh a materialized view completely as follows:

EXECUTE  
DBMS_SNAPSHOT.REFRESH('Materialized_VIEW_OWNER_NAME.Materialized_VIEW_NAME','COMPLETE');

Answer 3

When we have to use inbuilt procedures or packages we have to use "EXECUTE" command then it will work.

EX:

EXECUTE exec DBMS_MVIEW.REFRESH('v_materialized_foo_tbl');

Answer 4

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

Answer 5

If you're working with SQL Developer, you have to put the dbms_view in lowercase. The rest compiled fine for me although I haven't called the procedure from code yet.

CREATE OR REPLACE PROCEDURE "MAT_VIEW_FOO_TBL" AS 
BEGIN
  dbms_mview.refresh('v_materialized_foo_tbl');
END;

Answer 6

EXECUTE dbms_mview.refresh('view name','cf');