ora-00942 ora-06512 sys.dbms_snapshot

Solution

1) compile mv

2) recreate mv

---------
ORA-942 on Materialized View Refresh after Master Mview Recreated on Different Table [ID 867042.1]

exec dbms_mview.refresh(' ',C);

ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2563
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2776
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2745
ORA-06512: at "LDBO.SP_MVRKFOUTILIZATION", line 4
ORA-06512: at line 1

In this case, you do not know which of the tables within the materialized view is throwing the ORA-00942 error.  This user was advised to enable SQL Trace (TKPROF) st the session level to determine the exact table that is seeing the ORA-00942 error.


 could solve an occurance of this problem by calling
ALTER MATERIALIZED VIEW my_mview COMPILE;
before refreshing the mview. But I do not know why this was necessary in this case.


Possibly either of
a. the source table itself get's DROPped and reCREATEd -- and an MV Refresh within the DROP-CREATE cycle would error with ORA-942
b. grants on the source table get DROPped and reCREATEd -- and ....same as above ......
but dont know which table
The MV Definition (which is the QUERY column in USER_MVIEWS/DBA_MVIEWS) shows the query that the MV uses.
(QUERY is a LONG column so you must SET LONG 1000000 in sqlplus before querying the USER_MVIEWS/DBA_MVIEWS view



SELECT * FROM ALL_SYNONYMS WHERE SYNONYM_NAME IN ('DBMS_MVIEW',> 'DBMS_SNAPSHOT');

SELECT * FROM ALL_TAB_PRIVS_RECD WHERE GRANTEE = 'PUBLIC' AND TABLE_NAME = 'DBMS_SNAPSHOT';