Scheduled jobs in Oracle 11g

Scheduler in pl/sql Oracle 11g

Hi Code seeker,

I am explaining refresh materialized view in oracle using Schedule jobs.


Example 1

create materialized view M_CUST_ORDER as select customer_cd,order_no,sum(order_qty)
from sod_order group by customer_cd,order_no

select * from M_cust_order
 
Manual refreshing materialized view
 
execute dbms_mview.refresh('M_CUST_ORDER','C')

automatic refreshing materialized view using schedule jobs.

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'CUSTOMER_ORDER_MVIEW_REFRESH',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN execute    dbms_mview.refresh(''M_CUST_ORDER'',''C''); END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=hourly; byminute=0; bysecond=0;',
    enabled         => TRUE);
END;


Manual execute schedule jobs.

exec dbms_scheduler.run_job('CUSTOMER_ORDER_MVIEW_REFRESH');

Example 2
  
Schedule any pl/sql procedure.

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'TEST_SH2',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN alam.gen_xm_file_test_sh; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=minutely; interval=2; bysecond=0;',
    enabled         => TRUE);
END;
/

exec dbms_scheduler.run_job('TEST_SH2');

select * from DBA_SCHEDULER_JOBS where job_name like '%TEST_SH2%'

select * from DBA_SCHEDULER_JOB_RUN_DETAILS where job_name like '%TEST_SH2%'

TO Enable or disable schedule.

BEGIN
  DBMS_SCHEDULER.ENABLE('TEST_SH2');
END;


BEGIN
  DBMS_SCHEDULER.DISABLE('TEST_SH2');
END;

To check schedule details.

SELECT * FROM dba_scheduler_jobs WHERE job_name = 'TEST_SH2';
SELECT * FROM dba_scheduler_job_log WHERE job_name = 'TEST_SH2';
 
Or checking from JOB owner schema
 
SELECT * FROM user_scheduler_jobs WHERE job_name = 'TEST_SH2';
SELECT * FROM user_scheduler_job_log WHERE job_name = 'TEST_SH2';

Example 3

Schedule any batch file.

begin
 dbms_scheduler.create_schedule(
       schedule_name   => 'daily_at_4am_except_monday',
       repeat_interval => 'FREQ=DAILY; INTERVAL=1;
       BYDAY=TUE,WED,THU,FRI,SAT,SUN; BYHOUR=4',
       comments => 'schedule to run daily at 4am except on mondays');

  dbms_scheduler.create_program
    (  program_name   => 'backup_database',
       program_type   => 'EXECUTABLE',
       program_action => 'd:\oracle\product\admin\dw\scripts\backup_dw.bat > nul',
       enabled        => TRUE,
       comments       => 'Backup dw database using rman and then backup rman database via hot backup.'
    );

  dbms_scheduler.create_job (
    job_name=>'daily_backup',
    program_name =>'backup_database',
    schedule_name=> 'DAILY_AT_4AM_EXCEPT_MONDAY',
    enabled      => true,
    comments     => 'backs up the dw and rman databases daily at 4am
except on for mondays.'
  );
end;
/