La meilleure façon de réinitialiser une séquence Oracle à la valeur suivante dans une colonne existante?

pour une raison quelconque, les gens dans le passé ont inséré des données sans utiliser la séquence.NEXTVAL. Donc quand je vais utiliser sequence.NEXTVAL afin de peupler une table, j'obtiens une violation de PK, puisque ce nombre est déjà utilisé dans la table.

comment mettre à jour la valeur suivante pour qu'elle soit utilisable? Pour l'instant, j'insère encore et encore jusqu'à ce que ça marche (INSERT INTO tbl (pk) VALUES (sequence.NEXTVAL)), et qui synchronise le prochain épisode.

33
demandé sur Leniel Maccaferri 2011-05-23 19:08:01

5 réponses

Vous pouvez augmenter temporairement la taille du cache et faire une sélection factice 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;
60
répondu Basanth Roy 2018-04-11 14:15:49

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 (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;
14
répondu Cade Roux 2012-07-24 21:53:58

avec oracle 10.2 g:

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

définira la valeur de la séquence courante au max(pk) de votre table (c'est-à-dire que le prochain appel à NEXTVAL vous donnera le bon résultat); si vous utilisez Toad, appuyez sur F5 pour lancer la déclaration, et non F9, quelles pages la sortie (arrêtant ainsi l'incrément après, habituellement, 500 lignes). Bon côté: Cette solution n'est que DML, pas DDL. Seulement SQL et pas PL-SQL. Mauvais côté : Cette solution imprime des lignes max(pk) de sortie, i.e. est généralement plus lent que la séquence D'ALTER solution.

9
répondu tech 2013-10-25 08:25:40

Si vous pouvez compter sur une période de temps où la table est dans un état stable, sans nouvelles plaquettes en cours, 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 phase avec la table, sans laisser tomber/recréer/ré-accorder la séquence. Il n'utilise pas non plus de DDL, de sorte qu'aucune propagation implicite n'est effectuée. Bien sûr, vous allez devoir traquer et gifler les gens qui insistent pour ne pas utiliser la séquence pour remplir la colonne...

9
répondu DCookie 2016-04-16 01:46:08

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

j'ai ensuite importé des données de PROD à ma machine locale et insérée dans le PS_LOG tableau. Les données de Production ont plus que 20000 lignes avec la dernière LOG_ID (clé primaire) étant 20070. Après l'importation j'ai essayé d'insérer de nouvelles lignes dans ce tableau mais en enregistrant j'ai eu une exception comme celle-ci:

ORA-00001: unique constraint (LOG.PS_LOG_PK) violated

Sûrement cela a à voir avec la séquence PS_LOG_SEQ associé à la PS_LOG tableau. LAST_NUMBER entrait en collision avec des données que j'ai importées et qui avaient déjà utilisé la valeur ID suivante de PS_LOG_SEQ.

pour résoudre que j'ai utilisé cette commande pour mettre à jour la séquence à la dernière \ max(LOG_ID) + 1:

alter sequence PS_LOG_SEQ restart start with 20071;

cette commande réinitialise le LAST_NUMBER valeur et je pourrais alors insérer de nouvelles lignes dans la table. Pas plus de collision. :)

Remarque:alter sequence la commande est nouvelle dans Oracle 12c.

5
répondu Leniel Maccaferri 2014-06-20 03:56:18