Hi,

When I am trying to refresh a MV on demand, I am getting the below error.

*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2251
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2457
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2426
ORA-06512: at line 1
Here is the back ground.

master tables are owned by schema A

A view "V" is created in schema B using the tables in schema A

MV is owned by schema B

CREATE MATERIALIZED VIEW B.MV
REFRESH COMPLETE ON DEMAND
AS
SELECT *
FROM B.V
Now, I am using schema C to refresh the MV.

Schema B has select privilege on tables used in view with grant option. Schema C has alter any materialized view privilege, privilege on B.V.

When I execute only view it is fine. When I try to refresh MV I am getting the above error.

I am clueless at this point. Any help is highly appreciated.

Oracle - 10G R2

Thanks,