vendredi 30 décembre 2011

Resetter une sequence

Une des applications sur laquelle je travaille a planté en production à cause d'une séquence qui avait atteint une valeur trop importante pour la variable Delphi qui la stockait.
La solution consistait à resetter cette sequence.
Ce qu'il faut savoir c'est qu'avec Oracle il n'existe pas d'option RESET associée à la commande ALTER SEQUENCE. Pour resetter une sequence il faut la dropper et la recréer.
Toutefois, sur le site de Tom Kyte j'ai pu trouver une astuce pour resetter une sequence sans avoir à la dropper.
Cette solution consiste tout bêtement à modifier la valeur d'incrémentation pour décrementer la séquence de la valeur du NEXTVAL.

Voici ci-dessous les caractéristiques de ma séquence avant le reset:

SQL> select sequence_name, min_value,max_value, increment_by, cache_size,last_number
  2  from user_sequences where sequence_name='SEQ_MUTEX';

SEQUENCE_NAME    MIN_VALUE  MAX_VALUE INCREMENT_BY CACHE_SIZE LAST_NUMBER
--------------- ---------- ---------- ------------ ---------- -----------
SEQ_MUTEX                1 2147622080            1        500  2113785229

Il s'agit d'une sequence qui s'incrémente par une unité avec un cache de 500.

Je récupère ensuite la valeur du NEXTVAL:

SQL> select seq_mutex.nextval from dual;

   NEXTVAL
----------
2113785134

L'idée est de modifier la valeur de l'incrementation de la valeur du NEXTVAL moins 1 car le minvalue de ma sequence est à 1:

SQL> alter sequence seq_mutex increment by -2113785133;
alter sequence seq_mutex increment by -2113785133
*
ERROR at line 1:
ORA-04013: number to CACHE must be less than one cycle

SQL> alter sequence seq_mutex increment by -2113785133 NOCYCLE;

Sequence altered.

SQL> select seq_mutex.nextval from dual;

   NEXTVAL
----------
         1

On voit que ma séquence a été resetté à 1.
Vous noterez que j'ai dû mettre temporairement ma séquence en mode NOCYCLE pour éviter l'erreur ORA-04013. Une autre solution aurait consisté à laisser la séquence en mode CYCLE mais à réduire la taille du CACHE.

Ensuite je peux redéfinir ma séquence comme elle était auparavant c'est à dire en mode CYCLE et avec un INCREMENT à 1:

SQL> alter sequence seq_mutex cycle increment by 1 minvalue 1 cache 500;

Sequence altered.

SQL> select sequence_name, min_value,max_value, increment_by, cache_size,last_number
  2  from user_sequences where sequence_name='SEQ_MUTEX';

SEQUENCE_NAME    MIN_VALUE  MAX_VALUE INCREMENT_BY CACHE_SIZE LAST_NUMBER
--------------- ---------- ---------- ------------ ---------- -----------
SEQ_MUTEX                1 2147622080            1        500           2

Voilà comment j'ai pu resetter ma séquence sans la dropper.
Bien sûr il faut que pendant cette opération il n'y ait aucun autre session qui appelle cette séquence sinon on risque d'obtenir l'erreur suivante:

SQL> select seq_mutex.nextval from dual;
select seq_mutex.nextval from dual
       *
ERROR at line 1:
ORA-08004: sequence SEQ_MUTEX.NEXTVAL goes below MINVALUE and cannot be instantiated