Hello,
I have a materialized view that throws error in the alert log and doesnt get refreshed.
Alert error doesnt say much
Errors in file /oracle/app/oracle/diag/rdbms/ORA/trace/ORA_j002_4309026.trc:
ORA-12012: error on auto execute of job 749471
ORA-12008: error in materialized view refresh path
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2545
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2751
ORA-06512: at "SYS.DBMS_IREFRESH", line 685
ORA-06512: at "SYS.DBMS_REFRESH", line 195
The trace file says pretty much the same thing
*** 2010-10-26 21:33:08.117
*** SESSION ID:(4254.12255) 2010-10-26 21:33:08.117
*** CLIENT ID:() 2010-10-26 21:33:08.117
*** SERVICE NAME:(SYS$USERS) 2010-10-26 21:33:08.117
*** MODULE NAME:() 2010-10-26 21:33:08.117
*** ACTION NAME:() 2010-10-26 21:33:08.117
ORA-12012: error on auto execute of job 749471
ORA-12008: error in materialized view refresh path
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2545
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2751
ORA-06512: at "SYS.DBMS_IREFRESH", line 685
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1
Its complaining "table or view does not exist" , but dont know which table.
The Mview gets manuallly refreshed successfully. The query within the mview also runs fine without any problems.
Please share some thoughts how to troubleshoot this.
Thankyou