Oracle – How to Resolve Materialized View Error Log Table Issues

oracle

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?

Best Answer

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

Related Solutions

Oracle materialized view refresh schedule

Your expression can be as complicated as you'd like so you can certainly specify that the refresh should happen every day at 2 AM, i.e.

NEXT trunc(sysdate+1) + interval '2' hour

will specify that the refresh happens every day at 2 AM. The expression is evaluated at the conclusion of each refresh so you just need to ensure that the expression evaluates to whatever time you want at that particular instant in time.

Do I need to enumerate the columns used in a materialized view when creating the MV log

No, you do not need to enumerate the columns used in a materialized view when creating the materialized view log. In fact you cannot create a materialized view log using the primary key method and include all the columns because you would be including the primary key column itself, which is not allowed.

The concept of a materialized view log is to store the rowid or primary key of the data that changed. The refresh can then look up the entire record from the table. Adding specific columns to the WITH clause of the log explicitly records the data in the materialized view log itself. If your materialized view query is filtering on these columns or joining on them it could speed up the refresh.

Theoretically if all the columns used in the MV are in the log, then it should be able to refresh without referencing the table. The documentation does not indicate that this is being done. It would be interesting to trace this to see. Even if it does, the additional storage requirements may not make this route worth the trouble.

You should probably just create the materialized view logs without specifying a column list, like this:

CREATE MATERIALIZED VIEW LOG ON SCHEMA.TABLE_A 
   WITH ROWID, PRIMARY KEY, SEQUENCE INCLUDING NEW VALUES;

More Information:

Best Answer

Related Solutions

Oracle materialized view refresh schedule

Do I need to enumerate the columns used in a materialized view when creating the MV log

Related Question