DBMS_SCHEDULER

How to Create and Monitor DBMS_SCHEDULER Jobs

Step 0: Overview
Step 1: Create DBMS_SCHEDULER JOB
Step 2: Find the current status of job
Step 3: Find the SID
Step 4: How to find job is enabled(or)disabled
Step 5: How to Disbale DBMS_SCHEDULER job
Step 6: How to Enable DBMS_SCHEDULER job
Step 7: How to stop DBMS_SCHEDULER job
Step 8: How to start DBMS_SCHEDULER job
Step 9: How to change repeat_interval
Step 10: DBMS_SCHEDULER tables for Job monitoring


Step 0: Overview

In Oracle 10g the DBMS_JOB package is replaced by the DBMS_SCHEDULER package.

The DBMS_JOB package is now depricated and in Oracle 10g it's only provided for backward compatibility. 

From Oracle 10g the DBMS_JOB package should not be used any more, because is could not exist in a future version of Oracle.

With DBMS_SCHEDULER Oracle procedures and functions can be executed. Also binary and shell-scripts can be scheduled.


Step 1: Create DBMS_SCHEDULER JOB

-- Create Procedure --

SQL> show user
USER is "EP"
SQL> CREATE PROCEDURE SP1
AS
C INTEGER;
BEGIN
select count(*) INTO C
from test t1, test t2 where t1.c=t2.c and t1.c=1;
DBMS_OUTPUT.PUT_LINE(C);
END;
/
  2    3    4    5    6    7    8    9
Procedure created.

SQL>

-- How to create DBMS_SCHEDULER JOB ---
SQL> BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
  2    3         job_name => '"EP"."SP_RAJ"', <-- Job name
  4              job_type => 'STORED_PROCEDURE', <-- procedure
  5              job_action => 'EP.SP1', <-- exec Procedure
  6              number_of_arguments => 0,
  7              start_date => sysdate, <--- Run immediately after execution
  8              repeat_interval => 'FREQ=MINUTELY;INTERVAL=15',
  9              end_date => NULL,
 10              enabled => FALSE,
 11              auto_drop => FALSE,
 12              comments => 'Testing');
 13
 14      DBMS_SCHEDULER.enable(
 15               name => '"EP"."SP_RAJ"');
 16  END;
 17  /

PL/SQL procedure successfully completed.

SQL> 


Step 2: Find the current status of job

-- Please get the job_name from application team

SQL> select OWNER, JOB_NAME, STATE from dba_scheduler_jobs where JOB_NAME='SP_RAJ';

OWNER      JOB_NAME                       STATE
---------- ------------------------------ ---------------
EP         SP_RAJ                         RUNNING

SQL>


Step 3: Find the SID

SQL> col OWNER for a10
SQL> SELECT OWNER,SESSION_ID,JOB_NAME,RUNNING_INSTANCE FROM dba_scheduler_running_jobs where JOB_NAME='SP_RAJ';

OWNER      SESSION_ID JOB_NAME  RUNNING_INSTANCE
---------- ---------- --------- ----------------
EP                 39 SP_RAJ                   1

SQL>

SQL> col USERNAME for a10
SQL> col EVENT for a30
SQL> select sid, serial#, username, status, sql_id,to_char(LOGON_TIME, 'DD-MON-YYYY HH24:MI:SS') AS LOGON, event from v$session where SID='39';

       SID    SERIAL# USERNAME   STATUS   SQL_ID        LOGON                EVENT
---------- ---------- ---------- -------- ------------- -------------------- ------------------------------
        39         99 EP         ACTIVE   2p6db5q97bjvt 28-OCT-2016 23:47:02 asynch descriptor resize

SQL>

SQL> select program, module from v$session where sid='39';

PROGRAM                                          MODULE
------------------------------------------------ -----------------
oracle@rac2.rajasekhar.com (J000)                DBMS_SCHEDULER

SQL>

SQL> select vs.program, spid from v$session vs, v$process vp where vs.sid = 39 and vs.paddr = vp.addr;

PROGRAM                                          SPID
------------------------------------------------ ------------------------
oracle@rac2.rajasekhar.com (J000)                2278

SQL>

SQL> !ps -ef | grep 2278
oracle    2278     1 48 Oct28 ?        00:09:31 ora_j000_w148p <---
oracle    2559  4445  0 00:06 pts/2    00:00:00 /bin/bash -c ps -ef | grep 2278
oracle    2561  2559  0 00:06 pts/2    00:00:00 grep 2278

SQL>


Step 4: How to find currently job is enabled(or)disabled

SQL> select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name = 'SP_RAJ';

JOB_NAME                       ENABL
------------------------------ -----
SP_RAJ                         TRUE

SQL>


Step 5: How to Disbale DBMS_SCHEDULER job

BEGIN
      DBMS_SCHEDULER.disable(name=>'"EP"."SP_RAJ"', force => TRUE);
END;
/

-- OR --

exec sys.DBMS_SCHEDULER.disable(name=>'"EP"."SP_RAJ"', force => TRUE);


SQL> select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name = 'SP_RAJ';

JOB_NAME                       ENABL
------------------------------ -----
SP_RAJ                         FALSE

SQL>


Step 6: How to Enable DBMS_SCHEDULER job

BEGIN
      DBMS_SCHEDULER.enable(name=>'"EP"."SP_RAJ"');
END;
/

-- OR --

exec sys.DBMS_SCHEDULER.enable(name=>'"EP"."SP_RAJ"');

SQL> select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name = 'SP_RAJ';

JOB_NAME                       ENABL
------------------------------ -----
SP_RAJ                         TRUE

SQL>


Step 7: How to stop DBMS_SCHEDULER job

exec sys.dbms_scheduler.STOP_JOB(job_name=>'EP.SP_RAJ', force=>true);


Step 8: How to start DBMS_SCHEDULER job

exec sys.dbms_scheduler.RUN_JOB(job_name=>'EP.SP_RAJ');


Step 9: How to change repeat_interval

SQL> set lines 180
SQL> col REPEAT_INTERVAL for a30
SQL> select OWNER, JOB_NAME, STATE, REPEAT_INTERVAL from dba_scheduler_jobs where JOB_NAME='SP_RAJ';

OWNER      JOB_NAME        STATE           REPEAT_INTERVAL
---------- --------------- --------------- ------------------------------
EP         SP_RAJ          RUNNING         FREQ=MINUTELY;INTERVAL=15

SQL>

-- Current status is running, once job completed. Then do the below or if is it ok stop job then stop job.


SQL> select OWNER, JOB_NAME, STATE, REPEAT_INTERVAL from dba_scheduler_jobs where JOB_NAME='SP_RAJ';

OWNER      JOB_NAME        STATE           REPEAT_INTERVAL
---------- --------------- --------------- ------------------------------
EP         SP_RAJ          SCHEDULED       FREQ=MINUTELY;INTERVAL=15

SQL> 

SQL> exec sys.DBMS_SCHEDULER.disable(name=>'"EP"."SP_RAJ"', force => TRUE);

PL/SQL procedure successfully completed.

SQL> select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name = 'SP_RAJ';

JOB_NAME        ENABL
--------------- -----
SP_RAJ          FALSE

SQL>

SQL> BEGIN
        DBMS_SCHEDULER.SET_ATTRIBUTE (
        name => '"EP"."SP_RAJ"',
        attribute => 'repeat_interval',
        value => 'FREQ=MINUTELY;INTERVAL=30');
END;
/  2    3    4    5    6    7

PL/SQL procedure successfully completed.

SQL> select OWNER, JOB_NAME, STATE, REPEAT_INTERVAL from dba_scheduler_jobs where JOB_NAME='SP_RAJ';

OWNER      JOB_NAME        STATE           REPEAT_INTERVAL
---------- --------------- --------------- ------------------------------
EP         SP_RAJ          DISABLED        FREQ=MINUTELY;INTERVAL=30

SQL>

SQL> exec sys.DBMS_SCHEDULER.enable(name=>'"EP"."SP_RAJ"');

PL/SQL procedure successfully completed.

SQL> select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name = 'SP_RAJ';

JOB_NAME        ENABL
--------------- -----
SP_RAJ          TRUE

SQL> select OWNER, JOB_NAME, STATE, REPEAT_INTERVAL from dba_scheduler_jobs where JOB_NAME='SP_RAJ';

OWNER      JOB_NAME        STATE           REPEAT_INTERVAL
---------- --------------- --------------- ------------------------------
EP         SP_RAJ          SCHEDULED       FREQ=MINUTELY;INTERVAL=30

SQL>


Step 10: DBMS_SCHEDULER tables for Job monitoring

- To review the execution times of a job
SELECT JOB_NAME, STATUS, ERROR# FROM DBA_SCHEDULER_JOB_RUN_DETAILS;

- To review the errors of a Job:

select status, error#, substr(additional_info,1,500)
from dba_scheduler_job_run_details where job_name='SP_RAJ';

For jobs run by current user:

To view all running jobs --> USER_SCHEDULER_RUNNING_JOBS
To view job statuses --> USER_SCHEDULER_JOB_RUN_DETAILS
To view log details --> USER_SCHEDULER_JOB_LOG

For all jobs :

To view all running jobs --> DBA_SCHEDULER_RUNNING_JOBS
To view job statuses --> DBA_SCHEDULER_JOB_RUN_DETAILS
To view log details --> DBA_SCHEDULER_JOB_LOG

Monitoring job-scheduling

-- To show details on job run:
select log_date
, job_name
, status
, req_start_date
, actual_start_date
, run_duration
from dba_scheduler_job_run_details;

-- To show running jobs:

select job_name
, session_id
, running_instance
, elapsed_time
, cpu_used
from dba_scheduler_running_jobs;

-- To show job history:

select log_date
, job_name
, status
from dba_scheduler_job_log;

-- show all schedules:

select schedule_name, schedule_type, start_date, repeat_interval
from dba_scheduler_schedules;

-- show all jobs and their attributes:
select * from dba_scheduler_jobs;

-- show all program-objects and their attributes

select * from dba_scheduler_programs;

-- show all program-arguments:

select * from dba_scheduler_program_args;

-- For more help "desc dbms_scheduler;"

desc dbms_scheduler;

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Reference:

http://www.oradev.com/dbms_scheduler.html
http://www.vsevolod.com/magz/ORplSQLl/0596005873/oracleplsqldba-chp-8-sect-7.html
http://www.pafumi.net/Scheduler_DBMS_SCHEDULER.html

Thank you,
Rajasekhar Amudala