DBMS_SCHEDULER by example

==============================

Examples of using DBMS_SCHEDULER

==============================

Create Job

BEGIN 

 DBMS_SCHEDULER.CREATE_JOB ( 

  job_name => 'REFRESH_GSM_ROAMING_INFO_MV', 

  job_type => 'PLSQL_BLOCK', 

 job_action => 'BEGIN DBMS_MVIEW.REFRESH(''GSM_ROAMING_INFO_MV''); END;', 

 start_date => SYSDATE+1/1440, 

 repeat_interval => 'FREQ=HOURLY;INTERVAL=4', -- Run every 4 hours 

 enabled => TRUE, 

 comments => 'Refresh materialized view GSM_ROAMING_INFO_MV every 4 hours' ); 

END; 

/

Update Property

--Update job_action

BEGIN

    DBMS_SCHEDULER.SET_ATTRIBUTE(

        name      => 'MV_RF$J_0_S_326',

        attribute => 'job_action',

        value     => 'DBMS_REFRESH.REFRESH(''GSM_ROAMING_INFO_MV'');'

    );

END;

/

--Update repeat_interval

BEGIN

    DBMS_SCHEDULER.SET_ATTRIBUTE(

        name      => 'REFRESH_GSM_ROAMING_INFO_MV',

        attribute => 'repeat_interval',

        value     => 'FREQ=MINUTELY;INTERVAL=5'

    );

END;

/

Drop Job

DECLARE 

  CURSOR job_remove_cur IS

  SELECT job_name FROM USER_SCHEDULER_JOBS

   WHERE UPPER(job_action) LIKE '%GSM_ROAMING_INFO_MV%';

BEGIN

  FOR job_remove_rec IN job_remove_cur LOOP

    dbms_scheduler.drop_job(job_name => job_remove_rec.job_name);

    COMMIT;

  END LOOP;

END;

/

Monitor Job

SELECT job_action, 

       last_start_date,

       next_run_date, 

       failure_count, 

       run_count 

  FROM USER_SCHEDULER_JOBS 

 WHERE job_name = 'REFRESH_GSM_ROAMING_INFO_MV';