1

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

3

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

    • Thanks, but I have simplified the MV in the post, it actually joins many tables and so the ON COMMIT might not be feasible. Your first query gives me only the date and not the time. About your second query I guess I will need to repeat it as long as my view is in the list, which might be a way to solve the problem. Thanks, I'll let you know – Rossella Feb 2 '17 at 9:58
    • @Rossella I realize you said you simplified the mview, but even with joins (including outer joins) and aggregation, fast refresh may still be possible. Refer to the full list of restrictions here (12r1 docs): docs.oracle.com/database/121/DWHSG/basicmv.htm#i1007007. Be advised that the join syntax must be ANSI 89 (tablename, tablename, tablename...) and NOT ANSI 92 (with the JOIN keyword) for fast refresh to work. After it is fast refresh, see if fast on commit works for you. – Kris Johnston Feb 2 '17 at 14:25
    0

    I guess you could query the built-in views DBA_JOBS and DBA_JOBS_RUNNING with the following query:

    SELECT  
         A.JOB JOB#, 
         SCHEMA_USER MVIEW_OWNER, 
         substr(DECODE(SUBSTR(WHAT,INSTR(WHAT,'.',1,2)+2,INSTR(WHAT,'”',1,4)-4-INSTR(WHAT,'.',1,2)+2),NULL,SUBSTR(WHAT,1,80), 
         SUBSTR(WHAT,INSTR(WHAT,'.',1,2)+2,INSTR(WHAT,'”',1,4)-4-INSTR(WHAT,'.',1,2)+2)),0,80) JOB_DESCRIPTION, 
         to_char(LAST_DATE, 'yyyy-mm-dd hh24:mi:ss') LAST_RUN_DATE, 
         to_char(NEXT_DATE, 'yyyy-mm-dd hh24:mi:ss') NEXT_SCHED_RUN_DATE, 
         DECODE(BROKEN,'Y','YES','N','NO',' ') IS_BROKEN, 
         FAILURES,  
         RUNNING IS_RUNNING, 
         B.SID SID 
         FROM DBA_JOBS A LEFT OUTER JOIN (SELECT JOB,'YES' RUNNING,SID FROM DBA_JOBS_RUNNING ) B 
             ON A.JOB = B.JOB 
        ORDER BY SCHEMA_USER, JOB_DESCRIPTION;
    
    • Your query is only valid if the refresh was initiated using the Job Queue. – JSapkota Feb 2 '17 at 9:32
    -1

    https://docs.oracle.com/database/121/DWHSG/refresh.htm#DWHSG8373 looks like what you need. Unfortunately I don't know enough to be more specific.

    Update: I believe hot2use's answer may only apply for background updates, which I don't think your call will trigger. See https://docs.oracle.com/database/121/DWHSG/refresh.htm#DWHSG8369.
    My guess is that the way you are calling the refresh will complete the request before exiting the procedure, but that depends on what "the foreground process" means in Oracle.

    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.