DBMS SCHEDULAR

Creating Schedule:-
begin

dbms_scheduler.create_schedule( schedule_name => 'INTERVAL_EVERY_1_MINUTES', start_date => trunc(sysdate)+18/24, repeat_interval => 'freq=MINUTELY;interval=1', comments => 'Runtime: Every day all 1 minutes');

dbms_scheduler.create_schedule( schedule_name => 'INTERVAL_EVERY_1_SECONDLY', start_date => sysdate, repeat_interval => 'freq=SECONDLY;interval=1', comments => 'Runtime: Every day all 1 SECOND');
dbms_scheduler.create_schedule( schedule_name => 'INTERVAL_EVERY_0_SECONDLY', start_date => sysdate, repeat_interval => 'freq=SECONDLY;interval=0', comments => 'Runtime: Every day all 0 SECOND');
commit;
end;

Creating Program :-

begin -- Call a procedure of a database package

dbms_scheduler.create_program (program_name=> 'REPLAY_STANDARD_ORDER_JOB1', program_type=> 'PLSQL_BLOCK', program_action=> 'begin OE_REPLAY_PUB_PKG.replay_all(0,1); end;', enabled=>true, comments=>'Procedure to Replay Standard Orders for 0,1' );

-- Call a procedure of a database package

dbms_scheduler.create_program (program_name=> 'REPLAY_STANDARD_ORDER_JOB2', program_type=> 'PLSQL_BLOCK', program_action=> 'begin OE_REPLAY_PUB_PKG.replay_all(2,3); end;', enabled=>true, comments=>'Procedure to Replay Standard Orders 2,3' );

dbms_scheduler.create_program (program_name=> 'REPLAY_STANDARD_ORDER_JOB3', program_type=> 'PLSQL_BLOCK', program_action=> 'begin OE_REPLAY_PUB_PKG.replay_all(4,5); end;', enabled=>true, comments=>'Procedure to Replay Standard Orders 4,5' );

dbms_scheduler.create_program (program_name=> 'REPLAY_STANDARD_ORDER_JOB4', program_type=> 'PLSQL_BLOCK', program_action=> 'begin OE_REPLAY_PUB_PKG.replay_all(6,7); end;', enabled=>true, comments=>'Procedure to Replay Standard Orders for 6,7' );

dbms_scheduler.create_program (program_name=> 'REPLAY_STANDARD_ORDER_JOB5', program_type=> 'PLSQL_BLOCK', program_action=> 'begin OE_REPLAY_PUB_PKG.replay_all(8,9); end;', enabled=>true, comments=>'Procedure to Replay Standard Orders for 8,9' ); commit;
end;


Creating Jobs :--

begin -- Connect both dbms_scheduler parts by creating the final job

dbms_scheduler.create_job (job_name => 'REPLAY_JOB_FOR_0_AND_1', program_name=> 'REPLAY_STANDARD_ORDER_JOB1', schedule_name=>'INTERVAL_EVERY_0_SECONDLY', enabled=>true, auto_drop=>false, comments=>'Job to collect data about session values every 0 minutes');

dbms_scheduler.create_job (job_name => 'REPLAY_JOB_FOR_2_AND_3', program_name=> 'REPLAY_STANDARD_ORDER_JOB2', schedule_name=>'INTERVAL_EVERY_0_SECONDLY', enabled=>true, auto_drop=>false, comments=>'Job to collect data about session values every 0 minutes');

dbms_scheduler.create_job (job_name => 'REPLAY_JOB_FOR_4_AND_5', program_name=> 'REPLAY_STANDARD_ORDER_JOB3', schedule_name=>'INTERVAL_EVERY_0_SECONDLY', enabled=>true, auto_drop=>false, comments=>'Job to collect data about session values every 0 minutes');

dbms_scheduler.create_job (job_name => 'REPLAY_JOB_FOR_6_AND_7', program_name=> 'REPLAY_STANDARD_ORDER_JOB4', schedule_name=>'INTERVAL_EVERY_0_SECONDLY', enabled=>true, auto_drop=>false, comments=>'Job to collect data about session values every 0 minutes');

dbms_scheduler.create_job (job_name => 'REPLAY_JOB_FOR_8_AND_9', program_name=> 'REPLAY_STANDARD_ORDER_JOB5', schedule_name=>'INTERVAL_EVERY_0_SECONDLY', enabled=>true, auto_drop=>false, comments=>'Job to collect data about session values every 5 minutes');
end;

Run Jobs:-

begin dbms_scheduler.run_job('REPLAY_JOB_FOR_0_AND_1',TRUE); end;begindbms_scheduler.run_job('REPLAY_JOB_FOR_2_AND_3',TRUE); end;begindbms_scheduler.run_job('REPLAY_JOB_FOR_4_AND_5',TRUE); end;begindbms_scheduler.run_job('REPLAY_JOB_FOR_6_AND_7',TRUE); end;begindbms_scheduler.run_job('REPLAY_JOB_FOR_8_AND_9',TRUE);

end;

drop job:-
begin--DBMS_SCHEDULER.STOP_JOB(job_name=>'REPLAY_JOB_FOR_0_AND_1', force=>TRUE);--DBMS_SCHEDULER.STOP_JOB(job_name=>'REPLAY_JOB_FOR_2_AND_3', force=>TRUE);--DBMS_SCHEDULER.STOP_JOB(job_name=>'REPLAY_JOB_FOR_4_AND_5', force=>TRUE);--DBMS_SCHEDULER.STOP_JOB(job_name=>'REPLAY_JOB_FOR_6_AND_7', force=>TRUE);--DBMS_SCHEDULER.STOP_JOB(job_name=>'REPLAY_JOB_FOR_8_AND_9', force=>TRUE);
dbms_scheduler.drop_job('REPLAY_JOB_FOR_0_AND_1');
dbms_scheduler.drop_job('REPLAY_JOB_FOR_2_AND_3');
dbms_scheduler.drop_job('REPLAY_JOB_FOR_4_AND_5');
dbms_scheduler.drop_job('REPLAY_JOB_FOR_6_AND_7');
dbms_scheduler.drop_job('REPLAY_JOB_FOR_8_AND_9');

end;


Extra Information

DBMS_SCHEDULER.SET_ATTRIBUTE( name => 'INTERVAL_EVERY_MINUTE', attribute => 'repeat_interval', value => 'freq=MINUTELY;interval=2' );
select * from user_scheduler_job_log where job_name='REPLAY_JOB_FOR_0_AND_1';
Select * from DBA_SCHEDULER_JOB_RUN_DETAILS where (job_name = 'REPLAY_JOB_FOR_0_AND_1' OR job_name = 'REPLAY_JOB_FOR_2_AND_3') and run_duration <> '+00 00:00:00.000000' order by actual_start_date desc
Select * from DBA_SCHEDULER_JOB_RUN_DETAILS where job_name = 'REPLAY_JOB_FOR_0_AND_1' order by actual_start_date desc