DBMS_SCHEDULER in Oracle Database
Scheduler in the Oracle Database is done by DBMS_SCHEDULER package. This package can be used to create different types of jobs :-
1. Jobs having Program and Schedule already defined :-
Schedule for Job :-
BEGIN
— Create 24×7 Every 1 mins..
DBMS_SCHEDULER.CREATE_SCHEDULE (
schedule_name => ‘Every_1_mins_24x7’,
start_date => trunc(sysdate)+ 0/24,
repeat_interval => ‘freq=MINUTELY;interval=1’,
end_date => NULL,
comments => ‘Runtime: Every day after every 1 minutes’);
END;
Program for Job :-
begin
— Call a procedure of a database package
dbms_scheduler.create_program
(program_name=> ‘PROG_COUNT_NUM_OF_INACTIVE_SES’,
program_type=> ‘STORED_PROCEDURE’,
program_action=> ‘test.count_inactive_sessions_prc’,
enabled=>true,
comments=>’Procedure to count inactive sessions’
);
end;
Job having schedule and Program already defined :-
begin
— Connect both dbms_scheduler parts by creating the final job
dbms_scheduler.create_job
(job_name => ‘JOB_COUNT_INACTIVE_SESIONS’,
program_name=> ‘PROG_COUNT_NUM_OF_INACTIVE_SES’,
schedule_name=>’Every_1_mins_24x7′,
enabled=>true,
auto_drop=>false,
comments=>’Job to count inactive sessions’);
end;
2. Jobs having inline schedule and inline Program :-
BEGIN
— Job having inline schedule and inline program.
DBMS_SCHEDULER.create_job (
job_name => ‘JOB_COUNT_INACTIVE_SESIONS’,
job_type => ‘PLSQL_BLOCK’,
job_action => ‘BEGIN count_inactive_sessions_prc; END;’,
start_date => trunc(sysdate)+ 0/24, — start_date => ‘SYSTIMESTAMP’
repeat_interval => ‘freq=hourly; interval=1’,
end_date => NULL,
enabled => TRUE,
comments => ‘Job having inline schedule and inline program.’);
END;
/
3. Jobs having Program already defined and inline schedule
BEGIN
DBMS_SCHEDULER.create_job (
job_name => ‘JOB_COUNT_INACTIVE_SESIONS’,
program_name => ‘PROG_COUNT_NUM_OF_INACTIVE_SES’,
start_date => SYSTIMESTAMP,
repeat_interval => ‘freq=hourly; interval=1’,
end_date => NULL,
enabled => TRUE,
comments => ‘Job defined by Program already defined and inline schedule.’);
END;
/
4. Jobs having inline Program and Schedule already defined.
BEGIN
DBMS_SCHEDULER.create_job (
job_name => ‘JOB_GATHER_STAT_TABLE’,
schedule_name => ‘Every_1_hour_24x7’,
job_type => ‘PLSQL_BLOCK’,
job_action => ‘BEGIN DBMS_STATS.gather_table_stats(”test”,”COUNT_INACTIVE”,granularity=>”ALL”); END;’,
enabled => TRUE,
comments => ‘Job defined by inline Program and Schedule already defined.’);
END;
/
How to find out details of a JOB which has been run :-
select * from user_scheduler_job_run_details where JOB_NAME=’JOB_GATHER_STAT_TABLE’;
select * from user_scheduler_job_log where JOB_NAME=’JOB_GATHER_STAT_TABLE’ order by log_date desc;
