DBMS_scheduler JOBS

DBMS_scheduler JOBS

            DBAs are writing OS level scripts to execute different database related tasks and schedule it via crontab in Linux. The crontab jobs work perfectly well until Oracle released DBMS_SCHEDULER in 10g now that DBMS also act like crontab jobs scheduling method .

OS level job scheduling vs dbms_scheduling

·         DBMS_SCHEDULER syntax works same regardless of OS

·         Can run jobs based on database events

·         If DB is down during a job schedule, it will be executed again once DB starts up

·         You can run jobs on remote machines (11gR1 and above)

·         Schedule chain of jobs one after another

 

DBMS_scheduler jobs components

·         First define a program that is capable of executing PL/SQL script, shell script or stored procedures

·         Next, we need to define a schedule for the above program. The schedule contains execution frequency

·         Finally, we need to create a job with program name (created in first step) and schedule (created in second step) to DBMS_SCHEDULER

STEP 1 :Create program

A program tells DBMS_SCHEDULER as to what to execute. It is capable of executing

·         PL/SQL Block

·         Stored Procedure

·         OS level executable file

Sample program to execute a PL/SQL program:

SQL> BEGIN

  DBMS_SCHEDULER.create_program(

  program_name => 'plsql_program',

  program_type => 'PLSQL_BLOCK',

  program_action => 'BEGIN DBMS_STATS.gather_schema_stats(''HR''); END;',

  enabled => TRUE,

  comments => 'Program to gather HR user statistics');

  DBMS_SCHEDULER.enable (name=>'plsql_program');

END;

/

PL/SQL procedure successfully completed.

Drop, enable, disable program

Disable a program:

            If the program cannot be used some situations we hold the process mention the program name and disable it.

SQL> BEGIN

  DBMS_SCHEDULER.disable (name=>'plsql_program');

END;

/ 

PL/SQL procedure successfully completed.

Enable a program

            When ever we need same disabled program we want to  enable it.

SQL> BEGIN

  DBMS_SCHEDULER.enable (name=>'plsql_program');

END;

/ 

PL/SQL procedure successfully completed.

Drop a program:

            Once we drop a program cannot get it back its permeant delete method. Or else we need that program we use enable,disable option

SQL> BEGIN

  DBMS_SCHEDULER.drop_program(program_name=>'plsql_program');

END;

/ 

PL/SQL procedure successfully completed.

View program details

we must query DBA_SCHEDULER_PROGRAMS to view details about scheduled programs

set lines 999;

col owner for a20;

col program_name for a30;

SELECT owner, program_name, enabled FROM dba_scheduler_programs where program_name like '&enter_program_name';

STEP 2 – Create schedule

Schedules is what defines DBMS_SCHEDULER when to run a program and at what frequency / interval.

Below is a sample schedule that repeats every hour, exactly at 00 minutes and has no end date

SQL> conn vbt/vbt

Connected.

SQL> BEGIN

DBMS_SCHEDULER.create_schedule (

  schedule_name => 'hourly_sched',

  start_date => SYSTIMESTAMP,

  repeat_interval => 'freq=hourly; byminute=0',

  end_date => NULL,

  comments => 'Run every hour at 00 minutes everyday');

END;

/

PL/SQL procedure successfully completed.

View the scheduled programs:

SQL> set lines 999;

col schedule_name for a30;

SELECT owner, schedule_name from DBA_SCHEDULER_SCHEDULES;

OWNER                SCHEDULE_NAME

-------------------- ------------------------------

SYS                  DAILY_PURGE_SCHEDULE

SYS                  FILE_WATCHER_SCHEDULE

SYS                  PMO_DEFERRED_GIDX_MAINT_SCHED

SYS                  BSLN_MAINTAIN_STATS_SCHED

SYS                  TEST_HOURLY_SCHEDULE

SYS                  HOURLY_SCHED

VBT                  HOURLY_SCHED

7 rows selected.

Drop a scheduler program

SQL> BEGIN

  DBMS_SCHEDULER.drop_schedule (schedule_name => 'hourly_sched');

END;

/

PL/SQL procedure successfully completed.

STEP 3 – Create job

As mentioned earlier, you need not create program and schedule separately. You can define both program and schedule inside a job. But, it is always good to follow a standard.

DBMS_SCHEDULER is all about jobs. A job consists of a program and a schedule.

DBMS_SCHEDULER cannot execute a program or schedule independently. You must create a job and submit to the scheduler.

            First create a program and scheduler after that we create a job and assign it in the scheduler to automate and execute the task.

SQL> BEGIN

DBMS_SCHEDULER.create_job (

  job_name => 'test_sched_job',

  program_name => 'plsql_program',

  schedule_name => 'hourly_sched',

  enabled => TRUE,

  comments => 'My test scheduler job');

END;

/ 

PL/SQL procedure successfully completed.

SQL> set lines 999;

col job_name for a30;

select owner, job_name, enabled from dba_scheduler_jobs;

OWNER                JOB_NAME                                           ENABL

-------------------- ------------------------------                                     -----

SYS                      DBMS_JOB$_1                                           TRUE

ORACLE_OCM  MGMT_CONFIG_JOB                              TRUE

ORACLE_OCM  MGMT_STATS_CONFIG_JOB                 TRUE

VBT                      TEST_FULL_JOB_DEFINITION              TRUE

VBT                      DBMS_JOB$_2                                          TRUE

VBT                     TEST_SCHED_JOB                                    TRUE

28 rows selected.

Disable a job

SQL> BEGIN

  DBMS_SCHEDULER.disable (name=>'test_sched_job');

END;

/

PL/SQL procedure successfully completed.

Enable a job

SQL> BEGIN

  DBMS_SCHEDULER.enable (name=>'test_sched_job');

END;

/ 

PL/SQL procedure successfully completed.

Drop a job

BEGIN DBMS_SCHEDULER.drop_job (job_name=>'test_sched_job'); END; /

These views will show already currently running scheduled jobs:

• v$session
• dba_scheduler_running_chains
• dba_scheduler_running_jobs
• v$scheduler_running_jobs
• dba_scheduler_job_run_details

Check and monitor status of DBMS Schedulers jobs

Check Scheduler job detail in CDB

select CON_ID, JOB_NAME,JOB_TYPE,ENABLED, STATE,NEXT_RUN_DATE, REPEAT_INTERVAL from cdb_scheduler_jobs;

Monitor currently running jobs

SELECT job_name, session_id, running_instance, elapsed_time, FROM dba_scheduler_running_jobs;

View the job run details

select * from DBA_SCHEDULER_JOB_RUN_DETAILS;

View the job related logs:

select * from DBA_SCHEDULER_JOB_LOG;

Check all scheduler schedules

set pagesize 200

set lines 299

col START_DATE for a45

col REPEAT_INTERVAL for a45

col schedule_name for a34

select schedule_name, schedule_type, start_date, repeat_interval from dba_scheduler_schedules;

History of all scheduler job runs

set pagesize 299

set lines 299

col JOB_NAME for a24

col actual_start_date for a56

col RUN_DURATION for a34

select job_name,status,actual_start_date,run_duration from DBA_SCHEDULER_JOB_RUN_DETAILS order by ACTUAL_START_DATE desc;

Check log information for all Scheduler jobs

set pagesize 299

set lines 299

col job_name for a24

col log_date for a40

col operation for a19

col additional_info a79

select job_name,log_date,status,OPERATION,ADDITIONAL_INFO from dba_scheduler_job_log order by log_date desc;

Check all scheduler windows details

set pagesize 300 linesize 200

select * from dba_scheduler_windows;

********************************************************************************