如果 MV 獲得 need_compile 停滯狀態，如何創建調度程序以手動刷新物化視圖

如果停滯條件為NEED_COMPILE / STALE / UNUSABLE OR NOT FRESH.

我有這個 PL/SQL 的 MV：

CREATE MATERIALIZED VIEW "some_schema"."MV_TEST_BRO" ("some_field", "some_field", "some_field")
ORGANIZATION HEAP PCTFREE 10 PCTUSED 0 INITRANS 2 MAXTRANS 255 NOCOMPRESS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "some_tablespace" 
BUILD IMMEDIATE
USING INDEX 
REFRESH COMPLETE ON DEMAND
USING DEFAULT LOCAL ROLLBACK SEGMENT
USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
AS SELECT * FROM some_table;

順便說一句，我的 MV 太複雜了，放在這裡就簡單了。在我得到表主更新後，我用下面的查詢測試它以獲得我的 MV 的狀態停滯。

SELECT MVIEW_NAME, STALENESS, LAST_REFRESH_TYPE, COMPILE_STATE FROM USER_MVIEWS ORDER BY MVIEW_NAME;

而且，我創建 PL/SQL 來刷新該 MV，如下所示：

BEGIN
DBMS_REFRESH.make(
   name                 => 'some_schema.MINUTE_REFRESH',
   list                 => '',
   next_date            => SYSDATE,
   interval             => '/*1:Mins*/ SYSDATE + 1/(60*24)',
   implicit_destroy     => FALSE,
   lax                  => FALSE,
   job                  => 0,
   rollback_seg         => NULL,
   push_deferred_rpc    => TRUE,
   refresh_after_errors => TRUE,
   purge_option         => NULL,
   parallelism          => 4,
   heap_size            => NULL);
END;

BEGIN
DBMS_REFRESH.add(
   name => 'some_schema.MINUTE_REFRESH',
   list => 'some_schema.MV_TEST_BRO',
   lax  => TRUE);
END;

但是那個 dbms_refresh 的間隔根本不起作用。所以我創建了一些調度程序來完成這項工作，這是我的 PL/SQL ：

BEGIN
   sys.dbms_scheduler.create_job(
   job_name => '"some_schema"."REFRESH_MV"',
   job_type => 'PLSQL_BLOCK',
   job_action => 'begin
      DBMS_REFRESH.REFRESH(name => ''some_schema.MINUTE_REFRESH'');
   end;',
   schedule_name => '"some_schema"."EJS_MV_REFRESH"',
   job_class => '"DEFAULT_JOB_CLASS"',
   comments => 'Scheduler untuk merefresh MV pada schema some_schema',
   auto_drop => FALSE,
   enabled => FALSE);
   sys.dbms_scheduler.set_attribute( name => '"some_schema"."REFRESH_MV"', attribute => 'raise_events', value => dbms_scheduler.job_failed);
   sys.dbms_scheduler.set_attribute( name => '"some_schema"."REFRESH_MV"', attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_FAILED_RUNS);
   sys.dbms_scheduler.set_attribute( name => '"some_schema"."REFRESH_MV"', attribute => 'restartable', value => TRUE);
   sys.dbms_scheduler.enable( '"some_schema"."REFRESH_MV"' );
END;

到目前為止，這解決了我每 1 分鐘自動刷新 MV 的問題。但它根本沒有效果。所以，我的問題是：

如果狀態為，如何使其每 1 分鐘job_action執行一次？DBMS_REFRESH.REFRESH(name => ''some_schema.MINUTE_REFRESH'');``stallness is not FRESH

我不太了解如何使用指針或任何名稱來獲取查詢結果以檢查SELECT MVIEW_NAME, STALENESS, LAST_REFRESH_TYPE, COMPILE_STATE FROM USER_MVIEWS ORDER BY MVIEW_NAME;並使用此狀態來觸髮刷新，就像IF-THEN條件一樣。

請幫助我，如果你知道如何獲得它…

如果狀態停滯不是，我可以成功地為我的 MV 創建一個調度程序，以便每分鐘刷新一次FRESH。這是我的解決方案：

我創建了一個要在我的調度程序中使用的時間表：

BEGIN
   sys.dbms_scheduler.create_schedule(
   repeat_interval => 'FREQ=MINUTELY',
   start_date => to_timestamp_tz('2015-06-09 01:41:55 Asia/Jakarta', 'YYYY-MM-DD HH24:MI:SS TZR'),
   comments => 'Interval refresh 1 menit',
   schedule_name => '"SOME_SCHEMA"."REFRESH_MV"');
END;

之後我創建 DBMS_SCHEDULER 來創建這樣的工作：

BEGIN
sys.dbms_scheduler.create_job(
job_name => '"SOME_SCHEMA"."REFRESH_MV"',
job_type => 'PLSQL_BLOCK',
job_action => 'DECLARE
   mv_name USER_MVIEWS.MVIEW_NAME%type;
   mv_status USER_MVIEWS.STALENESS%type;
   mv_last USER_MVIEWS.LAST_REFRESH_TYPE%type;
   mv_state USER_MVIEWS.COMPILE_STATE%type;

   CURSOR check_mv
   IS SELECT MVIEW_NAME, STALENESS, LAST_REFRESH_TYPE, COMPILE_STATE FROM USER_MVIEWS WHERE MVIEW_NAME = ''MV_TEST_BRO'' ORDER BY MVIEW_NAME;

   BEGIN
       OPEN check_mv;

       LOOP
           FETCH check_mv INTO mv_name, mv_status, mv_last, mv_state;
           IF mv_status != ''FRESH'' THEN
               DBMS_REFRESH.REFRESH(name => ''SOME_SCHEMA.MINUTE_REFRESH'');
           ELSE
               EXIT;
           END IF;
           EXIT WHEN check_mv%NOTFOUND;

       END LOOP;

   CLOSE check_mv;
END;',
schedule_name => '"SOME_SCHEMA"."REFRESH_MV"',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'Refresh MV every 1 Minutes',
auto_drop => FALSE,
enabled => FALSE);
sys.dbms_scheduler.set_attribute( name => '"SOME_SCHEMA"."REFRESH_MV"', attribute => 'raise_events', value => dbms_scheduler.job_failed);
sys.dbms_scheduler.set_attribute( name => '"SOME_SCHEMA"."REFRESH_MV"', attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_FAILED_RUNS);
sys.dbms_scheduler.set_attribute( name => '"SOME_SCHEMA"."REFRESH_MV"', attribute => 'restartable', value => TRUE);
sys.dbms_scheduler.enable( '"SOME_SCHEMA"."REFRESH_MV"' );
END;

在作業操作中，我檢查了指定 MV 的停滯狀態，MV_TEST_BRO但您可以檢查您擁有的每個 MV 的狀態。如果作業失敗、日誌記錄級別和可重新啟動，我啟動了一些屬性來引發事件。

這項工作現在已經執行了一周，完全沒有問題。FRESH每次都是我的MV 。可能這篇文章會幫助另一個像我一樣面臨同樣問題的人。:D

引用自：https://dba.stackexchange.com/questions/103319