DBMS_SCHEDULER in Oracle Database


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;

  • January 17, 2019 | 24 views
  • Comments