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. À 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 5Version 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 SEQUENCEDonc ce qui donne pour soumettre un job qui va s'exécuter tous 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êmes 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$ UNIQUEIII-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 fourni 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é à 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.N. B. 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(*)
----------
0Chaque 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 arguments, 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; NIII-F. WHAT▲
Cette procédure sert à changer l'exécutable du Job.
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:00Dans 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
---------- -----------------------------------------------------------------------------------------
1245 trunc(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/1440III-K. INSTANCE▲
Ici nous sommes dans un environnement multiinstance.
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…