1

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?

migrated from serverfault.com Apr 27 '17 at 3:33

This question came from our site for system and network administrators.

2

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 04.42.58.546973 AM +05:45          SUCCEEDED
08-APR-17 02.38.12.843886 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 "SCHEMA.PACKAGE/PROCEDURE", line 70
ORA-06512: at line 1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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