DBMS_JOB ou comment schéduler des jobs sous Oracle

Apprenez à programmer des tâches planifiées sous Oracle 9i.
(Valable également pour les versions 8, 8i et 10g) .

I. Définition
II. Paramètres d'initialisation
III. Les Procédures de DBMS_JOB
III-A. SUBMIT
III-B. ISUBMIT
III-C. RUN
III-D. REMOVE
III-E. BROKEN
III-F. WHAT
III-G. NEXT_DATE
III-H. CHANGE
III-I. CHECK_PRIVS
III-J. INTERVAL
III-K. INSTANCE
III-L. USER_EXPORT
IV. Définition

I. Définition

Oracle gère une file d'attente interne pour les jobs. Ainsi vous pouvez soumettre à Oracle des jobs sans passer par ceux du système d'exploitation ( Crontab ou planificateur de Tâches).
Les files d'attente sont généralement utilisées pour gérer les fonctions de la base de données interne, telles que l'analyse des objets de la base de données, ou un grand classique : Le lancement de report STATSPACK. Concernant les travaux de maintenance de la production, il est préférable de les soumettre à la file d'attente du système d'exploitation. La situation optimale consistant à avoir un système de gestion de jobs centralisé.
Ces jobs alimentent la table SYS.JOB$ qui appartient au Tablespace SYSTEM.

Avant de commencer, je rappelle qu'il est indispensable de toujours valider ( commit ) lorsque l'on fait appel aux procédures du package DBMS_JOB (cf. les exemples).

II. Paramètres d'initialisation

Avant de pouvoir commencer à soumettre à Oracle vos jobs, il vous faut vous assurer que les paramètres d'initialisation sont corrects.
JOB_QUEUE_PROCESSES : ce paramètre va nous donner le nombre de jobs qu'il est possible de programmer. Ce paramètre est visible par les deux commandes suivantes:

SQL> show parameter job_queue_processes ; 

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     10
SQL> col value format a50
SQL> set linesize 250
SQL> select name , value  from v$parameter where name ='job_queue_processes' ; 

NAME                                                             VALUE
---------------------------------------------------------------- -----------------------------------
job_queue_processes                                              10

SQL>

Ce paramètre est apparu avec la version 7.3 d'oracle, et jusqu'à la version 8.0.4 il faisait partie des paramètres statiques. A savoir qu'un arrêt /relance de la base était nécessaire pour que le changement soit pris en compte.
Version 8i : Il est possible de faire un Alter system pour modifier la valeur de l'instance. Par la suite une modification du Init.ora sera nécessaire.

SQL> show release 
release 801070400
SQL> 

SQL> col value format a15
SQL> set linesize 250
SQL> select name , value  from v$parameter where name ='job_queue_processes' ; 

NAME                                                             VALUE
---------------------------------------------------------------- ---------------
job_queue_processes                                              10

SQL> alter system set job_queue_processes=5 ;

Système modifié.

SQL> select name , value  from v$parameter where name ='job_queue_processes' ; 

NAME                                                             VALUE
---------------------------------------------------------------- ---------------
job_queue_processes                                              5

Version 9i : Avec le SPFILE :

SQL> show parameter job_queue_processes 

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     10
SQL> alter system set job_queue_processes=5 scope =both ;

Système modifié.

SQL> show parameter job_queue_processes ;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     5
SQL>

JOB_QUEUE_INTERVAL : Durée minimum exprimée en secondes entre deux jobs.
Une modification de ce paramétre se fait forcément via l'init.ora.De plus il disparaît avec la version 9i.

JOB_QUEUE_KEEP_CONNECTIONS : Ce paramètre est par défaut positionné à FALSE, s'il est positionné à TRUE, il indique à Oracle de ne pas se déconnecter lorsque le job a été exécuté.
Ce paramètre disparaît avec la 8i.
Les vues DBA_JOBS, USER_JOBS, DBA_JOBS_RUNNING et USER_JOBS_RUNNING vont nous permettre d'avoir des informations pertinentes.
Il existe un script dbmsjob.sql qui est présent dans $ORACLE_HOME/rdbms/admin et qui permet de recréer le package dbms_job, ce package appartient à l'utilisateur SYS et donc il est conseillé de se connecter; en tant que SYS lors du lancement de ce dernier.
Pour pouvoir exécuter le package DMS_JOB il faut avoir le privilège requis.

SQL> GRANT EXECUTE ON dbms_job TO formation ; 

Autorisation de privilèges (GRANT) acceptée.

Pour connaître toutes les procédures du package, il suffit de faire un DESC

SQL> desc sys.DBMS_JOB
FUNCTION BACKGROUND_PROCESS RETURNS BOOLEAN
PROCEDURE BROKEN
...

III. Les Procédures de DBMS_JOB

III-A. SUBMIT

Cette procédure va nous permettre de soumettre un job dans la file d'attente :

PROCEDURE submit    ( job       OUT BINARY_INTEGER,
                        what      IN  VARCHAR2,
                        next_date IN  DATE DEFAULT sysdate,
                        interval  IN  VARCHAR2 DEFAULT 'null',
                        no_parse  IN  BOOLEAN DEFAULT FALSE,
                        instance  IN  BINARY_INTEGER DEFAULT any_instance,
                        force     IN  BOOLEAN DEFAULT FALSE );

Oracle gère le numéro de job par une séquence interne à oracle et qui appartient à SYS: JOBSEQ.

SQL> col object_name format a20 
SQL>  set linesize 250
SQL>  select owner , object_name , object_type from dba_objects where object_name ='JOBSEQ' ;

OWNER                          OBJECT_NAME          OBJECT_TYPE
------------------------------ -------------------- ------------------
SYS                            JOBSEQ               SEQUENCE

Donc ce qui donne pour soumettre un job qui va s'exécuter tout les jours à 9 heures :

SQL> DECLARE
  2  jobno number;
  3  begin
  4  dbms_job.submit(jobno, 'statspack.snap;',trunc(sysdate) + 1 + 9/24, ' trunc(sysdate) + 1 + 9/24
');
  5  commit ;
  6  end ;
  7  /

Procédure PL/SQL terminée avec succès.

Détails de quelques paramètres :
Job : Identifiant unique du job.
What : Code PL/SQL à exécuter.
Next date : Prochaine exécution.
Interval : Intervalle entre deux dates.
No_Parse : Le code PL/SQL doit-il être parsé avant exécution.
Comment déterminer la prochaine exécution ?
Avant tout, il ne faut pas oublier que dans INTERVAL, sysdate +1 représente demain à 00h00 et non pas dans 24 heures.
Pour les heures il faut préciser le nombre d'heures sur 24.

SYSDATE+18 /24 => représente demain à 18h00 ( car il y a 24 heures dans la journée) 

Sysdate + 37/48 => représente demain à 18h30 ( car il y 48 demi heures dans la journée ) 

Sysdate + 555/1440 => représente demain à 9h45 ( car il y a 1440 minutes dans la journée )

Quelques exemples de soumission :
Tous les jours de la semaine à 18 heures :

SQL> DECLARE
  2  jobno number;
  3  begin 
  4  DBMS_JOB.SUBMIT (jobno,'statspack.snap;',
  5   sysdate,
  6  'TRUNC(LEAST(NEXT_DAY(SYSDATE,''LUNDI''),
  7  NEXT_DAY(SYSDATE,''MARDI''),
  8  NEXT_DAY(SYSDATE,''MERCREDI''),
  9  NEXT_DAY(SYSDATE,''JEUDI''),              
 10  NEXT_DAY(SYSDATE,''VENDREDI'') )) + 18/24'); 
 11  commit ;
 12  end ;
 13  /

Procédure PL/SQL terminée avec succès.

III-B. ISUBMIT

Cette procédure effectue le même travail que SUBMIT, cependant sans pour autant se baser sur la séquence interne Oracle. L'utilisateur donne lui-même le numéro de job.

PROCEDURE isubmit    ( job       IN  BINARY_INTEGER,
                         what      IN  VARCHAR2,
                         next_date IN  DATE,
                         interval  IN  VARCHAR2 DEFAULT 'null',
                         no_parse  IN  BOOLEAN DEFAULT FALSE);

Nous ne faisons clairement plus appel à la séquence SYS.JOBSEQ mais oracle nous permet ainsi de gérer nous-même les numéros de JOB.
Exemple :

SQL> begin
  2   dbms_job.isubmit('1245', 'statspack.snap;',trunc(sysdate) + 1 + 9/24, ' trunc(sysdate) + 1 + 9
/24');
  3  commit ;
  4  end ; 
  5  /

Procédure PL/SQL terminée avec succès.

SQL> set linesize 250
SQL>  col what format a20
SQL>  select job , what from  user_jobs where job=1245
  2  ;

       JOB WHAT
---------- --------------------
      1245 statspack.snap;

SQL>

Attention à pas donner de numéro de job existant, car il existe un index unique sur la table sys.job$.

SQL> begin
  2   dbms_job.isubmit('1245', 'statspack.snap;',trunc(sysdate) + 1 + 9/24, ' trunc(sysdate) + 1 + 9
/24');
  3  commit ;
  4  end ; 
  5  /
begin
*
ERREUR à la ligne 1 :
ORA-00001: unique constraint (SYS.I_JOB_JOB) violated
ORA-06512: at "SYS.DBMS_JOB", line 97
ORA-06512: at line 2

SQL> SELECT index_name , owner , table_name , uniqueness FROM dba_indexes WHERE index_name ='I_JOB_J
OB'
  2  AND owner='SYS' ;

INDEX_NAME                     OWNER                          TABLE_NAME                     UNIQUENES
------------------------------ ------------------------------ ------------------------------ -------
I_JOB_JOB                      SYS                            JOB$                           UNIQUE

III-C. RUN

Cette procédure va nous permettre de lancer "à la main" le job.

PROCEDURE run       ( job       IN  BINARY_INTEGER,
                        force     IN  BOOLEAN DEFAULT FALSE);

En effet, après lui avoir fournit le numéro de job, Oracle va exécuter le job même si l'état de ce dernier est BROKEN. La procédure se compose de deux arguments, le premier où on lui indique le numéro de JOB et le second qui est positionnée à TRUE ou FALSE. Ce paramètre concerne l'exécution en arrière-plan ou pas.

SQL> begin 
  2  dbms_job.run('1245') ;
  3  end ;
  4  /

Procédure PL/SQL terminée avec succès.

NB : Cette procédure réinitialise l'état du package suite à son exécution.

III-D. REMOVE

Cette procédure va nous servir à supprimer un job.

PROCEDURE remove    ( job       IN  BINARY_INTEGER );

Comme nous le constatons, cette procédure ne contient qu'un seul argument le numéro de Job.
Cet identifiant unique va nous servir à déterminer le job que nous allons enlever de la file d'attente des traitements programmés.

SQL> ---- Remove all the jobs 
SQL>  select ' exec DBMS_JOB.REMOVE('||job||') ; ' from user_jobs ;

'EXECDBMS_JOB.REMOVE('||JOB||');'
------------------------------------------------------------------
 exec DBMS_JOB.REMOVE(55) ;
 exec DBMS_JOB.REMOVE(1245) ;

SQL>  exec DBMS_JOB.REMOVE(55) ;

Procédure PL/SQL terminée avec succès.

SQL>  exec DBMS_JOB.REMOVE(1245) ;

Procédure PL/SQL terminée avec succès.

SQL> select count (*) from user_jobs ;

  COUNT(*)
----------
         0

Chaque user doit supprimer ses propres JOBS, même un user avec les privilèges DBA ne peut le faire.

III-E. BROKEN

Cette procédure permet de changer le statut du job et donc de le passer à BROKEN, ainsi il ne sera plus lancé :

  PROCEDURE broken    ( job       IN  BINARY_INTEGER,
                        broken    IN  BOOLEAN,
                        next_date IN  DATE DEFAULT SYSDATE );

Il reçoit au minimum deux argument, le numéro du job et l'état du BROKEN soit TRUE soit FALSE :

SQL> select job , what , broken from user_jobs ; 

       JOB WHAT                 B
---------- -------------------- -
        53 statspack.snap;      N

SQL> exec dbms_job.broken (53,TRUE) ;

Procédure PL/SQL terminée avec succès.

SQL> select job , what , broken from user_jobs ; 

       JOB WHAT                 B
---------- -------------------- -
        53 statspack.snap;      Y

SQL>  exec dbms_job.broken (53,FALSE) ; 

Procédure PL/SQL terminée avec succès.

SQL> select job , what , broken from user_jobs ; 

       JOB WHAT                 B
---------- -------------------- -
        53 statspack.snap;      N

III-F. WHAT

Cette procédure sert à changer l'exécutable du Job.

Specification:
  PROCEDURE what      ( job       IN  BINARY_INTEGER,
                        what      IN  VARCHAR2 );

Ainsi tout en gardant le numéro de traitement et la fréquence d'exécution, on va pouvoir remplacer le code qui est exécuté.

SQL> select what , job from user_jobs ; 

WHAT                        JOB
-------------------- ----------
statspack.snap;            1245

SQL> begin
  2   dbms_job.what('1245', ' begin dbms_output.enable (500) ; dbms_output.put_line (''tes'') ;  end
 ; ');
  3  commit ;
  4  end ; 
  5  /

Procédure PL/SQL terminée avec succès.

SQL> select what , job from user_jobs ; 

WHAT                        JOB
-------------------- ----------
 begin dbms_output.e       1245
nable (500) ; dbms_o
utput.put_line ('tes
') ;  end ;

Nous avons besoin de renseigner deux paramètres, le numéro de Job et le nouveau traitement.

III-G. NEXT_DATE

Nous allons nous intéresser à la prochaine exécution et la modifier.

PROCEDURE next_date ( job       IN  BINARY_INTEGER,
                        next_date IN  DATE     );

Cette procédure a besoin du numéro de job et de la prochaine date d'exécution.

SQL>  alter session set NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS' ;

Session modifiée.

SQL> select job , what, next_date from user_jobs  ;

       JOB WHAT                 NEXT_DATE
---------- -------------------- -------------------
      1245 statspack.snap;      03/09/2005 09:00:00

SQL>    begin
  2      dbms_job.NEXT_DATE(1245,trunc(sysdate) + 1 + 10/24);
  3      commit ;
  4      end ;
  5  /

Procédure PL/SQL terminée avec succès.

SQL> select job , what, next_date from user_jobs  ;

       JOB WHAT                 NEXT_DATE
---------- -------------------- -------------------
      1245 statspack.snap;      03/09/2005 10:00:00

Dans cet exemple nous retardons la prochaine exécution d'une heure.

III-H. CHANGE

PROCEDURE change    ( job       IN  BINARY_INTEGER,
                        what      IN  VARCHAR2,
                        next_date IN  DATE,
                        interval  IN  VARCHAR2,
                        instance  IN  BINARY_INTEGER DEFAULT NULL,
                        force     IN  BOOLEAN DEFAULT FALSE);

Grâce à la procédure CHANGE nous allons pouvoir changer différents attributs du JOB.

III-I. CHECK_PRIVS

Nous allons tester ici les privilèges du user sur les différents objets.

PROCEDURE check_privs ( job IN BINARY_INTEGER );

Cette procédure n'existe plus à partir de la 8174 :

SQL> begin 
  2    dbms_job.check_privs(1245);
  3      commit ;
  4      end ; 
  5  /

PL/SQL procedure successfully completed.

III-J. INTERVAL

Changer le nombre d'exécutions du job.

PROCEDURE interval  ( job       IN  BINARY_INTEGER,
                        interval  IN  VARCHAR2 );

Un exemple :

SQL> select job , interval from user_jobs ;

       JOB INTERVAL
---------- -----------------------------------------------------------------------------------------
1245trunc(sysdate) + 1 + 9/24

SQL> SQL> begin 
  2  dbms_job.interval(1245, 'trunc(sysdate,''HH24'') + 1/24 + 35/1440' ) ;
  3  commit ;
  4  end ;
  5  /


Procédure PL/SQL terminée avec succès.

SQL> select job , interval from user_jobs ;

       JOB INTERVAL
---------- -----------------------------------------------------------------------------------------
      1245 trunc(sysdate,'HH24') + 1/24 + 35/1440

III-K. INSTANCE

Ici nous sommes dans un environnement multi-instances.

PROCEDURE instance ( job        IN BINARY_INTEGER,
                       instance   IN BINARY_INTEGER,
                       force      IN BOOLEAN DEFAULT FALSE);

En effet, dans un environnement RAC, nous allons avoir le choix de forcer l'exécution du job sur une instance plutôt qu'une autre. Si le Job doit se déclencher alors dans ce cas là Oracle va prendre une instance disponible au hasard.
Mais grâce à la vue v$instance, la possibilité de visualiser et de choisir son instance est possible. Il suffit pour cela de se servir de la procédure instance et de lui assigner le numéro d'instance.

Begin 
dbms_job.instance(1245,2,TRUE);
commit ;
end ;
/

III-L. USER_EXPORT

PROCEDURE user_export ( job    IN     BINARY_INTEGER,
                          mycall IN OUT VARCHAR2);

Permet de reproduire le texte afin de recréer le JOB, pour un éventuel export/import.

SQL> var varexp VARCHAR2(2000) 
SQL> begin
  2  dbms_job.user_export(1245,:varexp);
  3  end ; 
  4    
  5  /

Procédure PL/SQL terminée avec succès.

SQL> print varexp

VAREXP
----------------------------------------------------------------------------------------------------
dbms_job.isubmit(job=>1245,what=>'statspack.snap;',next_date=>to_date('2005-09-03:10:00:00','YYYY-MM

SQL>

IV. Définition

Job : Travail , tâche ...

Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée par les droits d'auteur. Copyright © 2005 Jaouad ZOUAGHI. Aucune reproduction, même partielle, ne peut être faite de ce site ni de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.