Developpez.com - Oracle
X

Choisissez d'abord la catégorieensuite la rubrique :


DBMS_JOB ou comment schéduler des jobs sous Oracle

Par Jaouad (jaouad.developpez.com/)
 

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 et 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.

Contacter le responsable de la rubrique Oracle