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