Meilleure façon de réinitialiser une séquence Oracle à la prochaine valeur dans une colonne existante ?

Question

Pour une raison quelconque, les gens dans le passé ont inséré des données sans utiliser sequence.NEXTVAL. Donc quand j'utilise sequence.NEXTVAL pour peupler une table, j'obtiens une violation de clé primaire, car ce numéro est déjà utilisé dans la table.

Comment puis-je mettre à jour la valeur suivante pour qu'elle soit utilisable? En ce moment, j'insère encore et encore jusqu'à ce que cela fonctionne (INSERT INTO tbl (pk) VALUES (sequence.NEXTVAL)), et cela synchronise le nextval.

Answer 1

Vous pouvez temporairement augmenter la taille du cache et effectuer une sélection fictive, puis réinitialiser la taille du cache à 1. Par exemple :

ALTER SEQUENCE mysequence INCREMENT BY 100;

select mysequence.nextval from dual;

ALTER SEQUENCE mysequence INCREMENT BY 1;

Answer 2

Dans mon cas, j'ai une séquence appelée PS_LOG_SEQ qui avait un LAST_NUMBER = 3920.

J'ai ensuite importé des données de PROD sur ma machine locale et inséré dans la table PS_LOG. Les données de production avaient plus de 20000 lignes avec le dernier LOG_ID (clé primaire) étant de 20070. Après l'importation, j'ai essayé d'insérer de nouvelles lignes dans cette table mais lors de l'enregistrement j'ai eu une exception comme celle-ci:

ORA-00001: contrainte d'unicité (LOG.PS_LOG_PK) violée

Cela a sûrement à voir avec la Séquence PS_LOG_SEQ associée à la table PS_LOG. Le LAST_NUMBER entrait en conflit avec les données que j'avais importées et qui avaient déjà utilisé la valeur ID suivante de la PS_LOG_SEQ.

Pour résoudre cela j'ai utilisé cette commande pour mettre à jour la séquence avec le dernier \ max(LOG_ID) + 1:

alter sequence PS_LOG_SEQ restart start with 20071;

Cette commande a réinitialisé la valeur LAST_NUMBER et j'ai pu alors insérer de nouvelles lignes dans la table. Plus de conflit. :)

Note: cette commande alter sequence est nouvelle dans Oracle 12c.

Note: ce article de blog documente que l'option ALTER SEQUENCE RESTART existe, mais à partir de la version 18c, n'est pas documentée; elle est apparemment destinée à un usage interne d'Oracle.

Answer 3

Ces deux procédures me permettent de réinitialiser la séquence et de réinitialiser la séquence en fonction des données d'une table (désolé pour les conventions de codage utilisées par ce client) :

CREATE OR REPLACE PROCEDURE SET_SEQ_TO(p_name IN VARCHAR2, p_val IN NUMBER)
AS
   l_num   NUMBER;
BEGIN
   EXECUTE IMMEDIATE 'select ' || p_name || '.nextval from dual' INTO l_num;

   -- Ajout de la vérification pour éviter "ORA-04002: L'INCREMENT doit être un entier non nul"
   IF (p_val - l_num - 1) != 0
   THEN
      EXECUTE IMMEDIATE 'alter sequence ' || p_name || ' increment by ' || (p_val - l_num - 1) || ' minvalue 0';
   END IF;

   EXECUTE IMMEDIATE 'select ' || p_name || '.nextval from dual' INTO l_num;

   EXECUTE IMMEDIATE 'alter sequence ' || p_name || ' increment by 1 ';

   DBMS_OUTPUT.put_line('Séquence ' || p_name || ' est maintenant à ' || p_val);
END;

CREATE OR REPLACE PROCEDURE SET_SEQ_TO_DATA(seq_name IN VARCHAR2, table_name IN VARCHAR2, col_name IN VARCHAR2)
AS
   nextnum   NUMBER;
BEGIN
   EXECUTE IMMEDIATE 'SELECT MAX(' || col_name || ') + 1 AS n FROM ' || table_name INTO nextnum;

   SET_SEQ_TO(seq_name, nextnum);
END;

Answer 4

Si vous pouvez compter sur une période où la table est dans un état stable sans nouveaux inserts en cours, cela devrait fonctionner (non testé) :

DÉCLARER
  last_used  NUMBER;
  curr_seq   NUMBER;
BEGIN
  SELECT MAX(pk_val) INTO last_used FROM your_table;

  BOUCLE
    SELECT your_seq.NEXTVAL INTO curr_seq FROM dual;
    SI curr_seq >= last_used THEN SORTIR;
    END IF;
  FIN BOUCLE;
END;

Cela vous permet de synchroniser la séquence avec la table sans la supprimer/recréer/accorder à nouveau la séquence. Cela n'utilise pas de DDL, donc aucun commit implicite n'est effectué. Bien sûr, vous devrez traquer et réprimander les personnes qui refusent d'utiliser la séquence pour peupler la colonne...

Answer 5

Avec oracle 10.2g :

select  level, sequence.NEXTVAL
from  dual 
connect by level <= (select max(pk) from tbl);

définira la valeur actuelle de la séquence sur max(pk) de votre table (c'est-à-dire que l'appel suivant à NEXTVAL vous donnera le bon résultat) ; si vous utilisez Toad, appuyez sur F5 pour exécuter l'instruction, pas F9, qui pagine la sortie (arrêtant ainsi l'incrémentation après, généralement, 500 lignes). Bon côté : cette solution est uniquement du DML, pas du DDL. Seulement SQL et pas de PL-SQL. Mauvais côté : cette solution imprime les lignes max(pk), c'est-à-dire qu'elle est généralement plus lente que la solution ALTER SEQUENCE.