DBMS_SCHEDULER examples

DBMS_SCHEDULER examples

May be interesting to some of you which haven't had the time testing and working with the DBMS_SCHEDULER. I collected some examples to show what is possible with that amazing tool.

DBMS_SCHEDULER is an internal Oracle package (since Version 10g) which provides database driven jobs.
It's divided into 3 parts:

• Time schedule part - dbms_scheduler.create_schedule
• Program declaration part - dbms_scheduler.create_program
• Job (conflation) part -dbms_scheduler.create_job

Examples of the dbms_scheduler.create_schedule part:

view plainprint?

1. begin  
2. -- daily from Monday to Sunday at 22:00 (10:00 p.m.)  
3. dbms_scheduler.create_schedule  
4. (schedule_name => 'INTERVAL_DAILY_2200',  
5.  start_date=> trunc(sysdate)+18/24, -- start today 18:00 (06:00 p.m.)  
6.  repeat_interval=> 'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI,SAT,SUN; BYHOUR=22;',  
7.  comments=>'Runtime: Every day (Mon-Sun) at 22:00 o'clock');  
8.   
9. -- run every hour, every day  
10. dbms_scheduler.create_schedule(    
11. schedule_name  => 'INTERVAL_EVERY_HOUR',    
12.   start_date    => trunc(sysdate)+18/24,    
13.   repeat_interval => 'freq=HOURLY;interval=1',    
14.   comments     => 'Runtime: Every day every hour');    
15.   
16. -- run every 5 minute, every day  
17. dbms_scheduler.create_schedule(  
18. schedule_name  => 'INTERVAL_EVERY_5_MINUTES',  
19.   start_date    => trunc(sysdate)+18/24,  
20.   repeat_interval => 'freq=MINUTELY;interval=5',  
21.   comments     => 'Runtime: Every day all 5 minutes');  
22.   
23. -- run every minute, every day  
24. dbms_scheduler.create_schedule(  
25. schedule_name  => 'INTERVAL_EVERY_MINUTE',  
26.   start_date    => trunc(sysdate)+18/24,  
27.   repeat_interval => 'freq=MINUTELY;interval=1',  
28.   comments     => 'Runtime: Every day every minute');  
29.   
30. -- run every Sunday at 18:00 (06:00 p.m.)  
31. dbms_scheduler.create_schedule  
32. (schedule_name => 'INTERVAL_EVERY_SUN_1800',  
33.  start_date=> trunc(sysdate)+18/24,  
34.  repeat_interval=> 'FREQ=DAILY; BYDAY=SUN; BYHOUR=18;',  
35.  comments=>'Runtime: Run at 6pm every Sunday');  
36. end;  

Example of the dbms_scheduler.create_program part:

view plainprint?

1. begin  
2. -- Call a procedure of a database package  
3. dbms_scheduler.create_program  
4. (program_name=> 'PROG_COLLECT_SESS_DATA',  
5.  program_type=> 'STORED_PROCEDURE',  
6.  program_action=> 'pkg_collect_data.prc_session_data',  
7.  enabled=>true,  
8.  comments=>'Procedure to collect session information'  
9.  );  
10. end;  

Example of the dbms_scheduler.create_job part:

view plainprint?

1. begin  
2. -- Connect both dbms_scheduler parts by creating the final job  
3. dbms_scheduler.create_job  
4.  (job_name => 'JOB_COLLECT_SESS_DATA',  
5.   program_name=> 'PROG_COLLECT_SESS_DATA',  
6.   schedule_name=>'INTERVAL_EVERY_5_MINUTES',  
7.   enabled=>true,  
8.   auto_drop=>false,  
9.   comments=>'Job to collect data about session values every 5 minutes');  
10. end;  

Examples to change dbms_scheduler settings:

view plainprint?

1. begin  
2. -- change start time  
3. DBMS_SCHEDULER.SET_ATTRIBUTE(  
4.    name => 'INTERVAL_EVERY_5_MINUTES',  
5.    attribute => 'start_date',  
6.    value => to_date('22.06.2009 12:15','dd.mm.yyyy hh24:mi')  
7. );  
8.   
9. -- change repeat interval  
10. DBMS_SCHEDULER.SET_ATTRIBUTE(  
11.    name => 'INTERVAL_EVERY_MINUTE',  
12.    attribute => 'repeat_interval',  
13.    value => 'freq=MINUTELY;interval=2'  
14. );   
15. end;  

Example to run job immediate:

view plainprint?

1. begin  
2. dbms_scheduler.run_job('JOB_COLLECT_SESS_DATA',TRUE);  
3. end;  

Example to restart job:

view plainprint?

1. begin  
2. dbms_scheduler.disable('JOB_COLLECT_INST_INFO');  
3. dbms_scheduler.enable('JOB_COLLECT_INST_INFO');  
4. end;  

Select job status:

view plainprint?

1. -- All jobs  
2. select * from user_scheduler_jobs;  
3.   
4. -- Get information to job  
5. select * from user_scheduler_job_log order by log_date desc;  
6.   
7. -- Show details on job run  
8. select * from user_scheduler_job_run_details;