How to create scheduler for manually refresh Materialized View if the MV get need_compile stallness status I've a problem to Automatically Refresh Materialized View, if the stallness condition is NEED_COMPILE / STALE / UNUSABLE OR NOT FRESH.

I've MV with this PL/SQL :

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;


btw, my MV is too complex to put it here so I made it simple in here.
After I got an update in table master I test it with below query to get status stallness of my MV.

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


And, I create PL/SQL to refresh that MV like this :

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

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


But the interval of that dbms_refresh is not working at all. So I created some scheduler to do the job, this is my PL/SQL :

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


So far this solve my problem to automatically refresh MV every 1 minute. But it's not effective at all. So, My Problem is :

How to make this job_action to execute DBMS_REFRESH.REFRESH(name =&gt; ''some_schema.MINUTE_REFRESH''); every 1 minute if the status of stallness is not FRESH ?

I don't know much about how to use pointer or whatever the name to get the result of query to check SELECT MVIEW_NAME, STALENESS, LAST_REFRESH_TYPE, COMPILE_STATE FROM USER_MVIEWS ORDER BY MVIEW_NAME; and use this status to trigger the refresh, it's just like IF-THEN condition.

Please help me, if you know how to get that...
I'm successfully able to create a scheduler for my MV to refresh every minutes if the status stallness is not FRESH. Here's my solutions :

I created a schedules to be used in my scheduler:

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


After that I create DBMS_SCHEDULER to create a job like this :

BEGIN
sys.dbms_scheduler.create_job(
job_name =&gt; '"SOME_SCHEMA"."REFRESH_MV"',
job_type =&gt; 'PLSQL_BLOCK',
job_action =&gt; '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 =&gt; ''SOME_SCHEMA.MINUTE_REFRESH'');
            ELSE
                EXIT;
            END IF;
            EXIT WHEN check_mv%NOTFOUND;

        END LOOP;

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


In the job action, I checked stallness status for specified MV like MV_TEST_BRO but you can check the status for every MV you had. And I activated some attribut to raise event if the job failed, logging level, and restartable.

This job was running for a week now and no problem at all. My MV always FRESH every time. May be this post will help another who face the same problem like me. :D