I have a number of materialized views that for various reasons on (rare) occasion will fail to materialize. Is there a database table that stores the error messages from the refresh attempts that I can query?

1 Answer 1


You can use the following query to check when the MVs were last refreshed.

SQL> select owner, mview_name, last_refresh_type, last_refresh_date
from dba_mviews/user_mviews;

If you were refreshing these MVs using DBMS_SCHEDULER:-

SQL> select log_date, status 
from dba_scheduler_job_run_details 
where job_name='JOB_NAME';

LOG_DATE                                     STATUS
--------------------------------------       ------------------------------
16-APR-17 AM +05:45          SUCCEEDED
08-APR-17 AM +05:45          FAILED

If you were refreshing these MVs using Job Queues/DBMS_JOB`:-

SQL> select job, last_date last_refresh,next_date next_refresh, total_time,broken, failures, what
from dba_jobs
where what like '%dbms_refresh%';

The reason of the job failure is written in alert_<SID>.log file as shown below as well as in a trace file referenced in the alert log file.

Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_j003_143260.trc:
ORA-12012: error on auto execute of job "SCHEMA"."JOB_NAME"
ORA-12008: error in materialized view refresh path
ORA-22992: cannot use LOB locators selected from remote tables
ORA-06512: at line 1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.