甲骨文DBMS_JOB.SUBMIT：混合同步和异步 [英] Oracle dbms_job.submit: Mixing Synchronous and Asynchronous

问题描述

我有7个需要按计划进行刷新物化视图。

I have 7 Materialized Views that need to be refreshed on a schedule.

其中五是数据源的独立，并且可以异步重建。我想你使用像汤姆描述的这里

Five of them are data source independent and could be rebuilt asynchronously. I'd like do use something like Tom described here

PROCEDURE refresh_Independent_MViews AS
l_job       BINARY_INTEGER;
BEGIN
  dbms_job.submit (l_job, 'DBMS_MVIEW.REFRESH(list => ''IndependentMView1'', method => ''C'') ;') ;
  dbms_job.submit (l_job, 'DBMS_MVIEW.REFRESH(list => ''IndependentMView2'', method => ''C'') ;') ;
  dbms_job.submit (l_job, 'DBMS_MVIEW.REFRESH(list => ''IndependentMView3'', method => ''C'') ;') ;
  dbms_job.submit (l_job, 'DBMS_MVIEW.REFRESH(list => ''IndependentMView4'', method => ''C'') ;') ;
  dbms_job.submit (l_job, 'DBMS_MVIEW.REFRESH(list => ''IndependentMView5'', method => ''C'') ;') ;
END refresh_Independent_MViews;

他们两个都依赖于一些前五MViews和需要等到那些已刷新。这最后两个彼此独立的并且可以在同一时间运行。

Two of them are dependent on some of the first five MViews and need to wait until those have been refreshed. These last two are independent of each other and could be run at the same time.

PROCEDURE refresh_Dependent_MViews AS
l_job       BINARY_INTEGER;
BEGIN
  dbms_job.submit (l_job, 'DBMS_MVIEW.REFRESH(list => ''DependentMView1'', method => ''C'') ;') ;
  dbms_job.submit (l_job, 'DBMS_MVIEW.REFRESH(list => ''DependentMView2'', method => ''C'') ;') ;
END refresh_Dependent_MViews;

问题：叫refresh_Independent_MViews旋转起来异步工作做的工作后很快返回，但是当个人异步工作都与他们的工作做的我不能告诉

The problem: Calling "refresh_Independent_MViews" returns very quickly after spinning up async jobs to do the work but I can't tell when the individual async jobs are all done with their work.

问题：有没有办法知道什么时候被DBMS_JOB.SUBMIT纺了异步工作都做这样我就可以知道什么时候开始了refresh_Dependent_MViews程序

The question: Is there a way to know when the async jobs spun up by dbms_job.submit are all done so I can know when to start the "refresh_Dependent_MViews" procedure?

推荐答案

最简单的方法是从 DBMS_JOB采取 l_job 输出参数.submit ，然后写一个循环，检查有多少的工作值在 dba_jobs ，退出时计数为0，否则通过为 DBMS_LOCK.SLEEP 呼吁的一段合理的时间睡觉。很显然，你需要避免为了捕捉所有五个工作覆盖当前的 l_job 变量。类似

The simplest possible approach would be to take the l_job output parameters from dbms_job.submit and then write a loop that checks how many of those job values are in dba_jobs, exits when the count is 0, and otherwise sleeps via a call to dbms_lock.sleep for a reasonable period of time. Obviously, you'd need to avoid overwriting the current l_job variable in order to capture all five jobs. Something like

CREATE TYPE num_tbl
    AS TABLE OF NUMBER;

PROCEDURE refresh_all_MViews AS
  l_job       BINARY_INTEGER;
  l_jobs      num_tbl;
BEGIN
  l_jobs.extend(5);
  dbms_job.submit (l_job, ...) ;
  l_jobs(1) := l_job;
  dbms_job.submit (l_job, ...) ;
  l_jobs(2) := l_job;
  dbms_job.submit (l_job, ...) ;
  l_jobs(3) := l_job;
  dbms_job.submit (l_job, ...) ;
  l_jobs(4) := l_job;
  dbms_job.submit (l_job, ...) ;
  l_jobs(5) := l_job;

  loop
    select count(*)
      into l_cnt
      from dba_jobs
     where job in (select column_value from table(l_jobs));

    if( l_cnt = 0 )
    then
      exit;
    end if;

    dbms_lock.sleep( 10 ); -- Sleep for 10 seconds
  end loop;

  refresh_Dependent_MViews;

END refresh_all_MViews;

现在，你可以明显修改 refresh_Independent_MViews 过程返回需要进行监控作业编号的集合，使 refresh_all_mviews 程序调用 refresh_independent_mviews ，实现了循环，然后调用 refresh_dependent_mviews 。

Now, you could obviously modify the refresh_Independent_MViews procedure to return the collection of job numbers that need to be monitored so that the refresh_all_mviews procedure calls refresh_independent_mviews, implements the loop, and then calls refresh_dependent_mviews.

您可以通过让你的工作写，记录成功或失败，或发送通过Oracle AQ一条消息，另一个进程监听，以揭开序幕依赖MVIEW刷新表变得更加复杂。这可能是在这种情况下没有必要，但如果你的依赖变得更加复杂可能。毫无疑问，你也可以创建一个能够为你做这个 DBMS_SCHEDULER 链。

You could get more sophisticated by having your jobs write to a table that records success or failure or sending a message via Oracle AQ that another process listens to in order to kick off the dependent mview refresh. That's probably not needed in this case but might be if your dependencies get more sophisticated. Undoubtedly, you could also create a dbms_scheduler chain that would do this for you.

这篇关于甲骨文DBMS_JOB.SUBMIT：混合同步和异步的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！