LLI Wiki — My Digital Knowledge Base

Oracle: list, run, disable and audit DBMS_SCHEDULER jobs

DBMS_SCHEDULER queries and calls to manage and monitor Oracle jobs.

The Oracle scheduler (DBMS_SCHEDULER) runs the jobs of the database. The queries and commands below list the jobs, start, stop, enable, disable, change or drop them, and review their history. Inspired by opencodez.com.

List the jobs

-- select to_char(col_name, 'yyyy-mm-dd hh24:mi:ss') from tab_name
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
set linesize 400 ;
set pagesize 10000;
column schema_name format a15 ;
column job_name format a20 ;
column job_style format a15 ;
column job_type format a20 ;
column job_action format a30;
column schedule format a40
select owner as schema_name,
       job_name,
       job_style,
       case when job_type is null
                 then 'PROGRAM'
            else job_type end as job_type,
       case when job_type is null
                 then program_name
                 else job_action end as job_action,
       to_char(start_date, 'yyyy-mm-dd hh24:mi') as  start_date,
       case when repeat_interval is null
            then schedule_name
            else repeat_interval end as schedule,
       to_char(last_start_date, 'yyyy-mm-dd hh24:mi') as  last_start_date,
       to_char(next_run_date, 'yyyy-mm-dd hh24:mi') as  next_run_date,
       state
from sys.all_scheduler_jobs
order by owner,
         job_name;

Shorter view, with the job comments:

SELECT owner, job_name, comments FROM dba_scheduler_jobs;

Start a job

BEGIN
    DBMS_SCHEDULER.RUN_JOB(job_name => '"SYS"."RUN_RMAN_JOB"', USE_CURRENT_SESSION => FALSE);
END;

Stop a job

exec dbms_scheduler.stop_job (job_name => '<job_name>');

Check the state of a job

select job_name, owner, enabled from dba_scheduler_jobs;

select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name = 'RUN_RMAN_JOB';

Disable a job

execute dbms_scheduler.disable('owner.job');
exec dbms_scheduler.disable('MAINT_JOB');

BEGIN
  DBMS_SCHEDULER.DISABLE('MAINT_JOB');
END;
/

Enable a job

exec dbms_scheduler.enable('MAINT_JOB');

BEGIN
  DBMS_SCHEDULER.ENABLE('MAINT_JOB');
END;
/

Job details

set linesize 400
set pagesize 1000
column job_name format a40
column repeat_interval format a80
SELECT
job_name
, TO_CHAR (last_start_date, 'YYYYMMDD HH24:MI:SS') last_start_date
--, cast(last_run_duration as INTERVAL DAY TO SECOND(0)) last_run_duration
,lpad(extract(hour from last_run_duration),2,'0')||':'||
       lpad(extract(minute from last_run_duration),2,'0')||':'||
       lpad(round(extract(second from last_run_duration)),2,'0') as duration
, TO_CHAR (next_run_date, 'YYYYMMDD HH24:MI:SS') next_run_date
, repeat_interval
FROM dba_scheduler_jobs
WHERE job_name ='RMAN_BACKUP_JOB';

Execution log of a job

Each execution leaves a record in DBA_SCHEDULER_JOB_LOG:

set linesize 300
column job_name format a30
column operation format a20
column status format a20
SELECT
job_name
, TO_CHAR ( log_date, 'YYYYMMDD HH24:MI')  log_date
, operation
,status
FROM dba_scheduler_job_log
WHERE job_name = '<job_name>';

To clear all the history:

exec dbms_scheduler.purge_log ();

Change a job

The attributes of a job are changed with SET_ATTRIBUTE:

BEGIN
dbms_scheduler.set_attribute (
name => '<job_name>'
, attribute => 'repeat_interval'
, value => 'freq = weekly; byday = sun ');
END;
/

Drop a job

exec dbms_scheduler.drop_job ('<job_name>');

Failed runs

set linesize 400
set pagesize 1000
column job_name format a40
column repeat_interval format a80
SELECT
	job_name
	, to_char( log_date,'YYYYMMDD HH24:MI:SS') log_date
	, status
	, to_char ( actual_start_date,'YYYYMMDD HH24:MI:SS') actual_start_date
	,lpad(extract(hour from run_duration),2,'0')||':'||
       lpad(extract(minute from run_duration),2,'0')||':'||
       lpad(round(extract(second from run_duration)),2,'0') as duration
	, cpu_used
FROM dba_scheduler_job_run_details
where status = 'FAILED';

Run history of one job:

SELECT job_name, log_date, status, actual_start_date, run_duration, cpu_used FROM dba_scheduler_job_run_details where job_name ='RMAN_BACKUP_JOB';
Mastodon LLIMastodon SocialFosstodon