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

Rigger

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. – Selfcontent

I tried with exec MAT_VIEW_FOO_TBL; also BEGIN DBMS_MVIEW.REFRESH('v_materialized_foo_tbl'); END; but didnt worked. – Rigger

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? – Selfcontent

and... you should call it with 'V_MATERIALIZED_FOO_TBL' not lowercase. – Selfcontent

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. – Rigger

can you elliminate the trailing ;? – Selfcontent

tried without ; but no success:( – Rigger

post the relevant code from zend Studio and tag the question with php and zend... – Selfcontent

Answer 1

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.

Intermingle

This works fine in an IDE like SQL Developer, but if you are executing it from code (like ODP.NET etc..) then it has to be wrapped in BEGIN & END as @Waqas Ali suggests. – Visakhapatnam

@TomHalladay Is there something wrong with using EXECUTE for that? (The raw function certainly didn't work from SQL Developer for me.) – Heinrike

Getting below error: REFRESH FAST can not be used for materialized views – Meatus

Answer 2

Run this script to refresh data in materialized view:

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

Kealey

Above code is tested various times, and it works fine, no exception/error. There may be some problem with your tool/mechane etc. – Kealey

If you have a schema, use the following dbms_snapshot.refresh('schema.view'); – Lurlenelurline

For those wondering (like me): DBMS_MVIEW is a synonym for DBMS_SNAPSHOT – Ariosto

Answer 3

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

Vullo

Answer 4

You can refresh a materialized view completely as follows:

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

Pushbike

Answer 5

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

Finnigan

Answer 6

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;

Mundell

Answer 7

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.

Blackmon

Answer 8

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

Pontificate

Answer 9

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

Spheroidal

Welcome to Stackoverflow. Please take some time to read how to write a good answer. read this and this – Congenital