How to refresh materialized view in oracle

59
votes

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

9
oraclematerialized-views
are you using exec DBMS_MVIEW.REFRESH('v_materialized_foo_tbl'); or exec MAT_VIEW_FOO_TBL or BEGIN DBMS_MVIEW.REFRESH('v_materialized_foo_tbl'); END;? Any of these should work. - Florin Ghita
I tried with exec MAT_VIEW_FOO_TBL; also BEGIN DBMS_MVIEW.REFRESH('v_materialized_foo_tbl'); END; but didnt worked. - Srinivas
I think you are executing it from php as sql statement. It should be executed as procedure. I don't know php. How you call those statements. From Toad/SQLDeveloper or with php? - Florin Ghita
and... you should call it with 'V_MATERIALIZED_FOO_TBL' not lowercase. - Florin Ghita
Yes iam executing these statements from Zend Studio. And i tried with capital letter BEGIN DBMS_MVIEW.REFRESH('V_MATERIALIZED_FOO_TBL'); END; where its giving new error ORA-06550: line 1, column 59: PLS-00103: Encountered the symbol "" when expecting one of the following: ; <an identifier> <a double-quoted delimited-identifier> The symbol "; was inserted before "" to continue. - Srinivas

9 Answers

51
votes

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.

60
votes

Run this script to refresh data in materialized view:

BEGIN
DBMS_SNAPSHOT.REFRESH('Name here');
END;
12
votes

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

8
votes

You can refresh a materialized view completely as follows:

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

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 => '?');
5
votes

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;
1
votes

Try using the below syntax:

Common Syntax:

begin
dbms_mview.refresh('mview_name');
end;

Example:

begin
dbms_mview.refresh('inv_trans');
end;

Hope the above helps.

0
votes

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

-1
votes

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');