Search code examples
oracleoracle11g

Fast refresh on Materialized view giving error

I have one MV created in Schema1 whose master table are present in Schema2. Both Schema1 and Schema2 are on the same DB.

The MV creation works fine, however when Fast refresh of the MV is performed, I get the below error:

begin
  DBMS_MVIEW.refresh('MV_NAME','F');
end;

Error report -
ORA-12008: error in materialized view refresh path
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2809
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3025
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2994
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.
Error starting at line : 1 in command -

If I try to refresh it using Complete refresh as in :

begin
  DBMS_MVIEW.refresh('MV_NAME','C');
end;

The refresh works fine, but I don't want to do this, because it is a performance hit.

Googled around this and found the following but nothing is working:

  1. Give explicit grants on the master table (Schema2) to MV owner (Schema1): Did this but refresh is failing.
  2. Dropped the MV and recreate, ( did this, the MV refreshed correctly for the first time, however when the same job is run again, the same error popped up).
  3. Checked that all the synonym are working fine.

I did not find any of the below objects:SNAP$_<mview_name>, MVIEW$_<mview_name>, <mowner>.<master>@<dblink>, <mowner>.MLOG$_<master>@<dblink> the objects

Please advice what else can be checked in this.

Solution

  • After doing some research on this, was able to figure out the reason why the refresh was not working. Posting it here so that its helpful for others also. So it goes like this: When the MV was created, I had created the MV logs as well in the other schema (Schema2). When the fast refresh was happening, it was referring to the MV logs table and Schema1 does not have access to the Schema2 MV logs table, that is why the error was coming.

    solution: Granted Schema1 select privilege on the MV logs tables present in schema 2.

    Thanks