How to know when a refresh of a materialized view has been completed

materialized-vieworacle

After a specific event(e.g. someone add new data into the database via a GUI), I need to refresh a materialized view that aggregates some data and only after that refresh is complete I have to query from the MW and to show in the GUI the updated results.

Now, my problem is that I need a way to know when the refresh is complete so that I can then query and be sure to obtain an up to date result.

The MV is specified in this way:

CREATE MATERIALIZED VIEW ASSAY
("TYPE","METHOD",COMPOUND_CORPORATE_ID,RESULTS_AVG,RESULTS_ST_DEV,RESULTS_N)
AS
select "TYPE","METHOD",AVG("RESULTS"), STDDEV("RESULTS") , COUNT("RESULTS")
from ASSAY_INHIB
where upper("VALIDATION")='YES'
group by "TYPE","METHOD",COMPOUND_CORPORATE_ID;
CREATE INDEX assays_inhib_parent ON ASSAY(compound_corporate_id);
ALTER MATERIALIZED VIEW ASSAY  REFRESH FORCE   ON DEMAND;

I call from the GUI a stored procedure with the command exec REFRESH_MV() and the stored procedure is like this :

CREATE  PROCEDURE REFRESH_MV () AS
BEGIN
    DBMS_MVIEW.REFRESH('ASSAY');
END REFRESH_MV;

The problem is that I don't know when I can query the MV and be sure that data are up to date. Can anybody help?

Thanks

Best Answer

You you have said-

After a specific event(e.g. someone add new data into the database via a GUI), I need to refresh a materialized view that aggregates some data and only after that refresh is complete I have to query from the MW and to show in the GUI the updated results...

You may use ON COMMIT refresh instead of ON DEMAND BUT I just said may be, not sure about your requirement and implementation.

ON COMMIT Refresh

A materialized view can be refreshed automatically using the ON COMMIT method. Therefore, whenever a transaction commits which has updated the tables on which a materialized view is defined, those changes are automatically reflected in the materialized view. The advantage of using this approach is you never have to remember to refresh the materialized view.

The following query can be used to know when the MV was last refreshed. 

SQL> alter session set nls_date_format='dd/mm/yy hh24:mi:ss';
SQL> select owner, mview_name, last_refresh_type, last_refresh_date
    from all_mviews;

Sample output:

USERNAME         M_VIEW_TEST        COMPLETE   02/02/17 01:01:19

You can also use v$mvrefresh dynamic performance view to know which MV is being refresh.

select currmvowner, currmvname from v$mvrefresh;

References:

  • ALL_MVIEWS
  • ON COMMIT Refresh

    • Related Question