Refresh MV on demand

Hi,

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

Quote:

---

*
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

Quote:

---

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,

grant SELECT on table (owned by A ) to 'C' and try again.

Thanks for reply Thomas.

I have already tried that with no luck.

I have recalled that, after the MV is created couple of master tables are moved from one TS to other TS. I am not sure if that creates any problem. So, planned to drop / recreate the MV tomorrow then try refresh again.

I will update the forum after testing.

Thanks,

Quote:

---

CREATE MATERIALIZED VIEW B.MV
REFRESH COMPLETE ON DEMAND
AS
SELECT *
FROM B.V

---

as the tables are owned by schema 'A' why it is "... FROM B.V", it should be 'A.V' ?

Hi Thomas,

View is already created in schema B.

Thanks,