sql - with - Meilleur moyen de réinitialiser une séquence Oracle à la valeur suivante dans une colonne existante?




sequence sql oracle (4)

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 au maximum (pk) de votre table (le prochain appel à NEXTVAL vous donnera le bon résultat); Si vous utilisez Toad, appuyez sur F5 pour exécuter l'instruction, pas sur F9, qui affiche la sortie (ce qui arrête l'incrément généralement après 500 lignes). Bon côté: cette solution est uniquement DML, pas DDL. Seulement SQL et pas de PL-SQL. Mauvais côté: cette solution imprime des lignes de sortie max (pk), c'est-à-dire qu'elle est généralement plus lente que la solution ALTER SEQUENCE.

Pour une raison quelconque, les utilisateurs ont déjà inséré des données sans utiliser sequence.NEXTVAL. Donc, quand je vais utiliser sequence.NEXTVAL afin de remplir une table, j'obtiens une violation PK, car ce nombre est déjà utilisé dans la table.

Comment puis-je mettre à jour la valeur suivante afin qu'elle soit utilisable? En ce moment, INSERT INTO tbl (pk) VALUES (sequence.NEXTVAL) encore et encore jusqu'à ce qu'il réussisse ( INSERT INTO tbl (pk) VALUES (sequence.NEXTVAL) ), et cela synchronise le nextval.


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'un tableau (excuses 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;

   -- Added check for 0 to avoid "ORA-04002: INCREMENT must be a non-zero integer"
   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('Sequence ' || p_name || ' is now at ' || 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;

Si vous pouvez compter sur une période où la table est dans un état stable sans nouvelle insertion, cela devrait le faire (non testé):

DECLARE
  last_used  NUMBER;
  curr_seq   NUMBER;
BEGIN
  SELECT MAX(pk_val) INTO last_used FROM your_table;

  LOOP
    SELECT your_seq.NEXTVAL INTO curr_seq FROM dual;
    IF curr_seq >= last_used THEN EXIT;
    END IF;
  END LOOP;
END;

Cela vous permet de remettre la séquence en synchronisation avec la table, sans supprimer / recréer / accorder de nouveau la séquence. Il n'utilise également pas de DDL, donc aucun commit implicite n'est effectué. Bien sûr, vous allez devoir traquer et gifler les gens qui insistent pour ne pas utiliser la séquence pour remplir la colonne ...


Vous pouvez augmenter temporairement la taille du cache et sélectionner un mannequin, puis réinitialiser la taille du cache à 1. Ainsi, par exemple,

ALTER SEQUENCE mysequence INCREMENT BY 100;

select mysequence.nextval from dual;

ALTER SEQUENCE mysequence INCREMENT BY 1;






sequence