Dear All,
When I refresh Materialized View (MV) from an anonymous block, then it refreshes w/o any issue.
BEGIN
--works fine
dbms_mview.refresh('MY_MV');
END;
/
Then I have created a procedure to refresh MV as follows..
CREATE OR REPLACE PROCEDURE refresh_mvs
AS
/* procedure to refresh MVs */
BEGIN
dbms_mview.refresh('MY_MV');
END refresh_mvs;
/
Procedure "refresh_mvs" has been created successfully. Now, when I try to execute the procedure
BEGIN
refresh_mvs;
END;
/
then, I am getting below error.
Error report -
ORA-12008: error in materialized view refresh path
ORA-00942: table or view does not exist
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2821
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3058
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3017
ORA-06512: at "REFRESH_MVS", line 5
ORA-06512: at line 2
12008. 00000 - "error in materialized view refresh path"
*Cause: Table SNAP$_<mview_name> reads rows from the view
MVIEW$_<mview_name>, which is a view on the master table
(the master may be at a remote site). Any
error in this path will cause this error at refresh time.
For fast refreshes, the table <master_owner>.MLOG$_<master>
is also referenced.
*Action: Examine the other messages on the stack to find the problem.
See if the objects SNAP$_<mview_name>, MVIEW$_<mview_name>,
<mowner>.<master>@<dblink>, <mowner>.MLOG$_<master>@<dblink>
still exist.
Any idea what could be going wrong? Thanks in advance for your help.
Database: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
Regards,
Hari
~ Happy New Year