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
This question is related to
oracle
materialized-views
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.
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');
Run this script to refresh data in materialized view:
BEGIN
DBMS_SNAPSHOT.REFRESH('Name here');
END;
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.
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
You can refresh a materialized view completely as follows:
EXECUTE
DBMS_SNAPSHOT.REFRESH('Materialized_VIEW_OWNER_NAME.Materialized_VIEW_NAME','COMPLETE');
EXECUTE dbms_mview.refresh('view name','cf');
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;
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 => '?');
Source: Stackoverflow.com