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

Aujourd'hui, dans Oracle 12c ou plus récent, vous avez probablement la colonne définie comme GENERATED ... AS IDENTITY, et Oracle se charge de la séquence elle-même.

Vous pouvez utiliser une instruction ALTER TABLE pour modifier "START WITH" de l'identité.

ALTER TABLE tbl MODIFY ("ID" NUMBER(13,0) GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 3580 NOT NULL ENABLE);

Answer 2

Je m'excuse de ne pas avoir de solution en une seule ligne, puisque mon programme s'exécute dans Typeorm avec node-oracle. Cependant, je pense que les commandes SQL suivantes pourraient aider à résoudre ce problème.

Obtenez le LAST_NUMBER de votre séquence.

SÉLECTIONNEZ SEQUENCE_NAME, LAST_NUMBER FROM ALL_SEQUENCES WHERE SEQUENCE_NAME = '${sequenceName}'

Obtenez la valeur de votre PK de la dernière ligne (dans ce cas, ID est le PK).

SÉLECTIONNEZ ID FROM ${tableName} ORDER BY ID DESC FETCH NEXT 1 ROWS ONLY

Enfin, mettez à jour LAST_NUMBER à la valeur + 1 :

ALTER SEQUENCE ${sequenceName} RESTART START WITH ${value + 1}

Answer 3

Dans mon cas, j'ai utilisé une approche pour réinitialiser la séquence à zéro et ensuite la régler de zéro au maximum de la table cible:

DECLARE
    last_val NUMBER;
    next_val NUMBER;
BEGIN
    SELECT MAX(id_field) INTO next_val FROM some_table;
    IF next_val > 0 THEN
        SELECT some_table_seq.nextval INTO last_val FROM DUAL;
        EXECUTE IMMEDIATE 'ALTER SEQUENCE some_table_seq INCREMENT BY -' || last_val || ' MINVALUE 0';
        SELECT some_table_seq.nextval INTO last_val FROM DUAL;
        EXECUTE IMMEDIATE 'ALTER SEQUENCE some_table_seq INCREMENT BY ' || next_val;
        SELECT some_table_seq.nextval INTO last_val FROM DUAL;
        EXECUTE IMMEDIATE 'ALTER SEQUENCE some_table_seq INCREMENT BY 1 MINVALUE 1';
    END IF;
END;

Answer 4

Je suppose que la meilleure solution est une procédure stockée pour mettre à jour la valeur actuelle des séquences.

J'utilise cette procédure stockée dans Oracle :

CREATE OR REPLACE PROCEDURE SEQ_UPDATE_NEXTVAL(seq_name IN VARCHAR2, table_name IN VARCHAR2, col_name IN VARCHAR2)
AS
    colCurrVal   NUMBER;
    seqCurrVal   NUMBER;
BEGIN
    EXECUTE IMMEDIATE 'SELECT MAX(' || col_name || ') AS n FROM ' || table_name INTO colCurrVal;

EXECUTE IMMEDIATE 'SELECT ' || seq_name || '.currval FROM dual' INTO seqCurrVal;

IF (colCurrVal - seqCurrVal) <= 0 THEN
    DBMS_OUTPUT.put_line('La séquence ' || seq_name || ' n'a pas besoin d'être incrémentée. currval : ' || seqCurrVal);
    return;
END IF;

EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || seq_name || ' increment by ' || (colCurrVal - seqCurrVal);

EXECUTE IMMEDIATE 'SELECT ' || seq_name || '.nextval FROM dual' INTO seqCurrVal;
EXECUTE IMMEDIATE 'SELECT ' || seq_name || '.currval FROM dual' INTO seqCurrVal;

EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || seq_name || ' increment by 1';

DBMS_OUTPUT.put_line('La séquence ' || seq_name || ' est maintenant à ' || seqCurrVal);
END;

Pour exécuter la procédure stockée :

CALL SEQ_UPDATE_NEXTVAL('NOM_DE_LA_SEQUENCE', 'NOM_DE_LA_TABLE', 'NOM_DE_LA_COLONNE');

(Désolé pour mon anglais, je suis d'accord, corrigez-moi s'il vous plaît)

Salutations.