在Oracle 11g中执行同步和异步MView刷新的更好方法是什么? [英] What is a better way to do synchronous and asynchronous MView refreshes in Oracle 11g?

查看：89 发布时间：2020/5/22 3:00:05 oracle asynchronous oracle11g synchronous

本文介绍了在Oracle 11g中执行同步和异步MView刷新的更好方法是什么?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有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.

其中两个依赖于前五个MView中的一些，并且需要等到刷新它们之后再进行操作.最后两个彼此独立，并且可以异步运行.

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 run asynchronously.

我最初是在此处问这个问题.我的计划是走DBMS_JOB.SUBMIT路线.有人向我解释说DBMS_JOB.SUBMIT是Oracle的8级解决方案，我应该考虑使用DBMS_SCHEDULER.

I originally asked this question here. My plan was to go the DBMS_JOB.SUBMIT route. It was explained to me that DBMS_JOB.SUBMIT is an Oracle 8-level approach to the problem and that I should look into using DBMS_SCHEDULER.

我的问题更改为:在Oracle 11g中执行同步和异步MView刷新的更好方法是什么?

My question changed to: What is a better way to do synchronous and asynchronous MView refreshes in Oracle 11g?

我想与大家分享我使用的DBMS_SCHEDULER解决方案，以防有人对使用Oracle 11g中的同步和异步方法调用的更好方法感兴趣.

I'd like to share the DBMS_SCHEDULER solution I used in case anyone is interested in a better approach to working with synchronous and asynchronous method calls in Oracle 11g.

推荐答案

TLDR:

使用DBMS_SCHEDULER.CREATE_PROGRAM创建命名程序.
使用DBMS_SCHEDULER.CREATE_CHAIN创建链.
使用DBMS_SCHEDULER.DEFINE_CHAIN_STEP定义链中的步骤，以调用在#1中创建的命名程序.
使用DBMS_SCHEDULER.DEFINE_CHAIN_RULE定义何时调用步骤(这是定义同步和异步调用的位置).
使用DBMS_SCHEDULER.CREATE_SCHEDULE创建时间表，以确定何时运行#6中创建的作业.
使用DBMS_SCHEDULER.CREATE_JOB创建一个作业，该作业将根据#5中创建的计划运行以执行#2中创建的链.

代码:

-- First: Create the programs to refresh the MViews
BEGIN
  -- Independent Programs
  DBMS_SCHEDULER.CREATE_PROGRAM (
      program_name => 'PROGRAM_REFRESH_MVIEW_I1', 
      program_action => 'BEGIN DBMS_MVIEW.REFRESH(list => ''IndependentMView1'', METHOD => ''C'') ; END;', 
      program_type => 'PLSQL_BLOCK', 
      number_of_arguments => 0, 
      comments => 'This Refreshes the IndependentMView1 MView.', 
      enabled => TRUE) ;
  DBMS_SCHEDULER.CREATE_PROGRAM (
      program_name => 'PROGRAM_REFRESH_MVIEW_I2', 
      program_action => 'BEGIN DBMS_MVIEW.REFRESH(list => ''IndependentMView2'', METHOD => ''C'') ; END;', 
      program_type => 'PLSQL_BLOCK', 
      number_of_arguments => 0, 
      comments => 'This Refreshes the IndependentMView2 MView.', 
      enabled => TRUE) ;
  DBMS_SCHEDULER.CREATE_PROGRAM (
      program_name => 'PROGRAM_REFRESH_MVIEW_I3', 
      program_action => 'BEGIN DBMS_MVIEW.REFRESH(list => ''IndependentMView3'', METHOD => ''C'') ; END;', 
      program_type => 'PLSQL_BLOCK', 
      number_of_arguments => 0, 
      comments => 'This Refreshes the IndependentMView3 MView.', 
      enabled => TRUE) ;
  DBMS_SCHEDULER.CREATE_PROGRAM (
      program_name => 'PROGRAM_REFRESH_MVIEW_I4', 
      program_action => 'BEGIN DBMS_MVIEW.REFRESH(list => ''IndependentMView4'', METHOD => ''C'') ; END;', 
      program_type => 'PLSQL_BLOCK', 
      number_of_arguments => 0, 
      comments => 'This Refreshes the IndependentMView4 MView.', 
      enabled => TRUE) ;
  DBMS_SCHEDULER.CREATE_PROGRAM (
      program_name => 'PROGRAM_REFRESH_MVIEW_I5', 
      program_action => 'BEGIN DBMS_MVIEW.REFRESH(list => ''IndependentMView5'', METHOD => ''C'') ; END;', 
      program_type => 'PLSQL_BLOCK', 
      number_of_arguments => 0, 
      comments => 'This Refreshes the IndependentMView5 MView.', 
      enabled => TRUE) ;

  -- Dependent Programs
  DBMS_SCHEDULER.CREATE_PROGRAM (
      program_name => 'PROGRAM_REFRESH_MVIEW_D1', 
      program_action => 'BEGIN DBMS_MVIEW.REFRESH(list => ''DependentMView1'', METHOD => ''C'') ; END;', 
      program_type => 'PLSQL_BLOCK', 
      number_of_arguments => 0, 
      comments => 'This Refreshes the DependentMView1 MView.', 
      enabled => TRUE) ;
  DBMS_SCHEDULER.CREATE_PROGRAM (
      program_name => 'PROGRAM_REFRESH_MVIEW_D2', 
      program_action => 'BEGIN DBMS_MVIEW.REFRESH(list => ''DependentMView2'', METHOD => ''C'') ; END;', 
      program_type => 'PLSQL_BLOCK', 
      number_of_arguments => 0, 
      comments => 'This Refreshes the DependentMView2 MView.', 
      enabled => TRUE) ;
END;

/

-- Next: Create the chain to control the refresh steps
BEGIN
  DBMS_SCHEDULER.CREATE_CHAIN (
  chain_name => 'REFRESH_MVIEWS_CHAIN', 
  rule_set_name => NULL, 
  evaluation_interval => NULL, 
  comments => 'Refresh the Materialized Views in the correct order.') ;
END;

/

  -- Next: Create the steps used to call the programs to refresh the MViews. 
  -- Note: Referenced programs must be enabled.
BEGIN
  DBMS_SCHEDULER.DEFINE_CHAIN_STEP ('REFRESH_MVIEWS_CHAIN', 'stepI1', 'PROGRAM_REFRESH_MVIEW_I1') ;
  DBMS_SCHEDULER.DEFINE_CHAIN_STEP ('REFRESH_MVIEWS_CHAIN', 'stepI2', 'PROGRAM_REFRESH_MVIEW_I2') ;
  DBMS_SCHEDULER.DEFINE_CHAIN_STEP ('REFRESH_MVIEWS_CHAIN', 'stepI3', 'PROGRAM_REFRESH_MVIEW_I3') ;
  DBMS_SCHEDULER.DEFINE_CHAIN_STEP ('REFRESH_MVIEWS_CHAIN', 'stepI4', 'PROGRAM_REFRESH_MVIEW_I4') ;
  DBMS_SCHEDULER.DEFINE_CHAIN_STEP ('REFRESH_MVIEWS_CHAIN', 'stepI5', 'PROGRAM_REFRESH_MVIEW_I5') ;

  -- stepD1 is dependent on IndependentMView1, IndependentMView2, IndependentMView3
  DBMS_SCHEDULER.DEFINE_CHAIN_STEP ('REFRESH_MVIEWS_CHAIN', 'stepD1', 'PROGRAM_REFRESH_MVIEW_D1') ;
  -- stepD2 is dependent on IndependentMView1, IndependentMView4
  DBMS_SCHEDULER.DEFINE_CHAIN_STEP ('REFRESH_MVIEWS_CHAIN', 'stepD2', 'PROGRAM_REFRESH_MVIEW_D2') ;
END;

/

-- Next: Define rules for the chain.  This is where we establish the 
-- synchronous and asynchronous order of things. (i.e. where the magic happens)
BEGIN
  -- First, start all independent steps asynchronously
  DBMS_SCHEDULER.DEFINE_CHAIN_RULE ('REFRESH_MVIEWS_CHAIN', 'TRUE', 'START stepI1, stepI2, stepI3, stepI4, stepI5') ;
  -- Next, start dependent steps as their related independent steps complete.
  DBMS_SCHEDULER.DEFINE_CHAIN_RULE ('REFRESH_MVIEWS_CHAIN', 'stepI1 COMPLETED AND stepI2 COMPLETED AND stepI3 COMPLETED', 'START stepD1') ;
  DBMS_SCHEDULER.DEFINE_CHAIN_RULE ('REFRESH_MVIEWS_CHAIN', 'stepI1 COMPLETED AND stepI4 COMPLETED', 'Start stepD2') ;
  -- Last, define when the chain is complete.
  -- In this case, we're done when both dependent steps and the one independent step that no other steps are dependent upon are all complete.
  DBMS_SCHEDULER.DEFINE_CHAIN_RULE ('REFRESH_MVIEWS_CHAIN', 'stepI5 COMPLETED AND stepD1 COMPLETED AND stepD2 COMPLETED', 'END') ;

  -- Enable the chain
  DBMS_SCHEDULER.ENABLE ('REFRESH_MVIEWS_CHAIN') ;
END;

/

-- Next: create a schedule to run every 30 minutes at the top and bottom of every hour
BEGIN
  DBMS_SCHEDULER.CREATE_SCHEDULE ( 
      schedule_name => 'THIRTY_MINUTE_SCHEDULE', 
      repeat_interval => 'FREQ=MINUTELY;INTERVAL=30', 
      start_date => TO_TIMESTAMP_TZ ('2015-11-2 0:0:00.000000000 UTC', 'YYYY-MM-DD HH24:MI:SS.FF TZR'), 
      comments => 'Fires at the top and bottom of every hour') ;
END;

/

-- Lastly: Create a job to start the REFRESH_MVIEWS_CHAIN chain based on the THIRTY_MINUTE_SCHEDULE created above.
BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
      job_name => 'REFRESH_MVIEWS_JOB', 
      job_type => 'CHAIN', 
      job_action => 'REFRESH_MVIEWS_CHAIN', 
      schedule_name => 'TEN_TILL_TOP_BOTTOM_SCHEDULE', 
      number_of_arguments => 0, 
      enabled => FALSE, 
      auto_drop => FALSE, 
      comments => 'Refresh the Materialized Views');

  DBMS_SCHEDULER.SET_ATTRIBUTE (
      name => 'REFRESH_MVIEWS_JOB', 
      attribute => 'logging_level', 
      value => DBMS_SCHEDULER.LOGGING_OFF) ;

  -- Enable the refresh job
  DBMS_SCHEDULER.ENABLE (name => 'REFRESH_MVIEWS_JOB') ;
END;

/

一路有用的链接:

https://stackoverflow.com/a/16416525/384884
http://docs.oracle.com/cd/B28359_01/server.111/b28310/schedadmin006.htm#BAJHFHCD
http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sched.htm#i1011194
http://docs.oracle.com/cd/B19306_01/server.102/b14231/scheduse.htm#CHDGAJAG
https://docs.oracle.com/cd/B28359_01/server.111/b28310/scheduse009.htm#CHDCFBHG
http://dba.fyicenter.com/faq/oracle/PL-SQL-Named-Program-Unit.html

这篇关于在Oracle 11g中执行同步和异步MView刷新的更好方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文

在Oracle 11g中执行同步和异步MView刷新的更好方法是什么? [英] What is a better way to do synchronous and asynchronous MView refreshes in Oracle 11g?

问题描述

推荐答案

相关文章

数据库最新文章

热门教程

热门工具

登录关闭

在Oracle 11g中执行同步和异步MView刷新的更好方法是什么? [英] What is a better way to do synchronous and asynchronous MView refreshes in Oracle 11g?

问题描述

推荐答案

相关文章

数据库最新文章

热门教程

热门工具

登录 关闭

登录关闭