3 Replies Latest reply: Jun 20, 2014 1:34 AM by spajdy RSS

    dbms_scheduler

    941949

      hi ,

           what's up,guys?i need your favor,i scheduled my materialized view for refreshing using dbms_scheduler but it is not updating my view whenever there is an update in my table,kindly help.

      thanks in advance.

      sql>CREATE MATERIALIZED VIEW LOG ON t1;

       

      sql>create materialized view t1mv refresh on demand

          as

          (select a.ename,a.job,b.dname from t1 a,dept b where a.deptno=b.deptno);

       

      BEGIN

        DBMS_SCHEDULER.CREATE_JOB (

          JOB_NAME        => 't1_MV_REFRESH',

          JOB_TYPE        => 'PLSQL_BLOCK',

          JOB_ACTION        => 'BEGIN mv_refresh_job(''EXEC DBMS_MVIEW.REFRESH(''T1MV'', ''F'')''); END;',

          START_DATE      => SYSTIMESTAMP,

          REPEAT_INTERVAL => 'freq=minutely; bysecond=0;',

          END_DATE        => NULL,

          ENABLED         => TRUE,

          COMMENTS        => 'Refreshes Materialized Views on a Scheduled Basis');

      END;

       

      thanks and regards.

       

      os;rhel6

      db:11.2.0.3

        • 1. Re: dbms_scheduler
          GregV

          Hi,

           

          Have you checked the outcome of the job? What does it say?

          • 2. Re: dbms_scheduler
            941949

            hello,

                    i checked the output ,there is no change in the materialized view data.how can i solve this issue ?

             

            thanks & rregards.

            • 3. Re: dbms_scheduler
              spajdy

              We don't know what your procedure mv_refresh_job is doing.

              Why you don't simply call dbms_mview.refresh directly ?

              BEGIN
                DBMS_SCHEDULER.CREATE_JOB (
                  JOB_NAME        => 't1_MV_REFRESH',
                  JOB_TYPE        => 'PLSQL_BLOCK',
                  JOB_ACTION        => 'BEGIN DBMS_MVIEW.REFRESH(''T1MV'', ''F''); END;',
                  START_DATE      => SYSTIMESTAMP,
                  REPEAT_INTERVAL => 'freq=minutely; bysecond=0;',
                  END_DATE        => NULL,
                  ENABLED         => TRUE,
                  COMMENTS        => 'Refreshes Materialized Views on a Scheduled Basis');
              END;
              

              or you can use refresh clause when creating MV.

              When you call dbms_mview.refresh('T1MV','F'); from SQL*Plus what's the result ?