Oracle Materialized View refresh error or success

Question

We have a Materialized view that is created for remote database view (db link). The mt view is refreshed (Complete) every night. We have a requirement to provide a client with daily email status of the refresh whether the refresh is successful or not (for any reason). Currently the refresh is happening through DBMS_JOB. How can the system check automatically the status of the refresh and act upon it? If I create a procedure that calls DBMS_MVIEW.REFRESH (my mt view,'C') will it fail if the call to refresh is not successful (e.g. network issues)? Am I able to catch it in EXCEPTION and log the error? Also, is DBMS_ERRLOG.CREATE_ERROR_LOG useful in refreshing MT view?

Thank you

Answer 1

A few things:

You really should be using dbms_scheduler instead of dbms_job anyways since dbms_job has been deprecated as of 10g (provided you are on at least 10g... if not, you may want to consider upgrading).

While you can use dbms_scheduler to schedule mview refreshes, mviews have that capability already built in. The below command will schedule the mview to refresh (complete) every day at 1AM:

ALTER MATERIALIZED VIEW <mviewname>
 REFRESH COMPLETE
 NEXT (TRUNC (SYSDATE+1) + 1/24);

The syntax for the interval can be a little tricky until you get used to it... I would start with the Oracle docs.

To check on when the mview was last refreshed:

select owner, mview_name, last_refresh_date  from dba_mviews;

Therefore, if you are refreshing the mview every morning, you can set up a scheduler job to send an email if the mview was or was not refreshed that morning (based on the results of the above query).