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.
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;
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;
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.
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...
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.