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';