debugMinor

Oracle Materialized View Error Log Table?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

errorlogmaterializedvieworacletable

Problem

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?

Solution

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_.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

Code Snippets

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

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

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

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

Context

StackExchange Database Administrators Q#172115, answer score: 6

Revisions (0)

No revisions yet.