DBMS_SCHEDULER in Oracle Database

Scheduler in  Oracle Database is done by DBMS_SCHEDULER package. This package can be used to create different types of jobs :-

1. Jobs having inline schedule and inline Program.
2. Jobs having Program and Schedule already defined.
3. Jobs having Program already defined and inline schedule.
4. Jobs having inline Program and Schedule already defined.

1. Jobs having Program and Schedule already defined :-

Schedule for Job :-

BEGIN

-- Create 24x7 Every 1 mins..

DBMS_SCHEDULER.CREATE_SCHEDULE (

schedule_name => 'Every_1_mins_24x7',     --######## Not more than 30 words########

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',     --######## Not more than 30 words########

 program_type=> 'STORED_PROCEDURE', 

 program_action=> 'amit.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',      --######## Not more than 30 words########

  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(''AMIT'',''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;

Different Types of Programs :-

1. Stored Procedure

begin 

-- Call a procedure of a database package 

dbms_scheduler.create_program 

(program_name=> 'PROG_COUNT_NUM_OF_INACTIVE_SES',     --######## Not more than 30 words########

 program_type=> 'STORED_PROCEDURE', 

 program_action=> 'amit.count_inactive_sessions_prc', 

 enabled=>true, 

 comments=>'Procedure to count inactive sessions' 

 ); 

end; 
/

2. PLSQL Block :-

begin 

-- Call a procedure of a database package 

dbms_scheduler.create_program 

(program_name=> 'PROG_COUNT_NUM_OF_INACTIVE_SES',     --######## Not more than 30 words########

 program_type=> 'PLSQL_BLOCK', 

 program_action=> 'BEGIN DBMS_STATS.gather_table_stats(''AMIT'',''COUNT_INACTIVE'',granularity=>''ALL''); END;', 

 enabled=>true, 

 comments=>'Procedure to count inactive sessions' 

 ); 

end; 
/

3. Shell Script

begin 

-- Executing a shell file

dbms_scheduler.create_program 

(program_name=> 'PROG_COUNT_NUM_OF_INACTIVE_SES',     --######## Not more than 30 words########

 program_type=> 'EXECUTABLE', 

 program_action=> '/u01/oracle/count_inactive.sh', 

 enabled=>true, 

 comments=>'shell script to count inactive sessions' 

 ); 

end; 
/

How to find out details of a Program :-

SELECT owner, program_name, enabled FROM dba_scheduler_programs;

How to Drop a Program/Job/Schedule :-

exec dbms_scheduler.drop_job('JOB_GATHER_STAT_TABLE', TRUE);

exec dbms_scheduler.drop_program('PROG_COUNT_NUM_OF_INACTIVE_SES', TRUE);

exec dbms_scheduler.drop_SCHEDULE('Every_1_hour_24x7', TRUE);

How to Restart a JOB :-

Begin 

dbms_scheduler.disable('JOB_GATHER_STAT_TABLE'); 

dbms_scheduler.enable('JOB_GATHER_STAT_TABLE'); 

end; 

How to run a JOB manually :-

begin 

dbms_scheduler.run_job('JOB_GATHER_STAT_TABLE',TRUE); 

end;

I hope this article helped you.

Regards,
Amit Rath