LLI Wiki — My Digital Knowledge Base

Oracle : lister, lancer, désactiver et auditer les jobs DBMS_SCHEDULER

Requêtes et appels DBMS_SCHEDULER pour gérer et superviser les jobs Oracle.

Le planificateur Oracle (DBMS_SCHEDULER) exécute les jobs de la base. Les requêtes et commandes ci-dessous permettent d'inventorier les jobs, de les démarrer, arrêter, activer, désactiver, modifier ou supprimer, et de consulter leur historique. Inspiré de opencodez.com.

Inventaire des 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;

Vue plus courte, avec les commentaires des jobs :

SELECT owner, job_name, comments FROM dba_scheduler_jobs;

Démarrer un job

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

Arrêter un job

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

Vérifier l'état d'un job

select job_name, owner, enabled from dba_scheduler_jobs;

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

Désactiver un job

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

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

Activer un job

exec dbms_scheduler.enable('MAINT_JOB');

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

Détail d'un job

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

Journal d'exécution d'un job

Chaque exécution laisse une trace dans 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>';

Pour effacer tout l'historique :

exec dbms_scheduler.purge_log ();

Modifier un job

Les attributs d'un job se modifient avec SET_ATTRIBUTE :

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

Supprimer un job

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

Historique des exécutions en échec

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

Historique d'un job précis :

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