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

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, Jul 19 '12 at 06:18
I tried with exec MAT_VIEW_FOO_TBL; also BEGIN DBMS_MVIEW.REFRESH('v_materialized_foo_tbl'); END; but didnt worked. — Srinivas, Jul 19 '12 at 06:24
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, Jul 19 '12 at 06:31
and... you should call it with 'V_MATERIALIZED_FOO_TBL' not lowercase. — Florin Ghita, Jul 19 '12 at 06:32
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: ; The symbol "; was inserted before "" to continue. — Srinivas, Jul 19 '12 at 06:39
post the relevant code from zend Studio and tag the question with php and zend... — Florin Ghita, Jul 19 '12 at 11:58

score 69 · Answer 1 · edited Jun 26 '16 at 17:12

69

Run this script to refresh data in materialized view:

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

edited Jun 26 '16 at 17:12

Rosdi Kasim

24,267
23
130
154

answered Jan 22 '13 at 07:26

Waqas Ali

1,441
2
20
25

1

Above code is tested various times, and it works fine, no exception/error. There may be some problem with your tool/mechane etc. – Waqas Ali Oct 02 '13 at 10:46
3

If you have a schema, use the following `dbms_snapshot.refresh('schema.view');` – Zeus Jul 18 '19 at 13:25

score 57 · Accepted Answer · edited Dec 19 '12 at 05:11

57

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.

edited Dec 19 '12 at 05:11

afuzzyllama

6,538
5
47
64

answered Dec 19 '12 at 04:50

fahim ashraf

594
6
3

4

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. – Tom Halladay Apr 22 '14 at 05:50
1

@TomHalladay Is there something wrong with using `EXECUTE` for that? (The raw function certainly didn't work from SQL Developer for me.) – jpmc26 Jun 17 '14 at 08:46
Getting below error: REFRESH FAST can not be used for materialized views – Subhadeep Ray Jun 30 '22 at 09:36

score 18 · Answer 3 · answered Oct 06 '14 at 22:57

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

score 8 · Answer 4 · edited Feb 24 '16 at 15:12

8

You can refresh a materialized view completely as follows:

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

edited Feb 24 '16 at 15:12

Serban Tanasa

3,592
2
23
45

answered Jan 20 '15 at 07:39

Yasir Meraj

81
1
1

score 8 · Answer 5 · answered Sep 02 '16 at 23:21

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

score 5 · Answer 6 · answered Jul 14 '14 at 14:17

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;

score 1 · Answer 7 · edited Apr 11 '16 at 01:15

1

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.

edited Apr 11 '16 at 01:15

pfnuesel

14,093
14
58
71

answered Apr 11 '16 at 01:10

Vikash Prasad

11
1

score 1 · Answer 8 · answered Feb 14 '19 at 09:45

1

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

answered Feb 14 '19 at 09:45

issam

93
3

score -1 · Answer 9 · answered Feb 09 '17 at 06:51

-1

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

answered Feb 09 '17 at 06:51

moses

1

4

Welcome to Stackoverflow. Please take some time to read how to write a good answer. read [this](http://meta.stackexchange.com/a/7659/338114) and [this](http://stackoverflow.com/help/how-to-answer) – Sourav Ghosh Feb 09 '17 at 07:10

How to refresh materialized view in oracle

9 Answers9

Linked