Comment réinitialiser une séquence dans Oracle?

Dans PostgreSQL , je peux faire quelque chose comme ceci:

ALTER SEQUENCE serial RESTART WITH 0; 

Y a-t-il un équivalent Oracle?

Voici une bonne procédure pour réinitialiser une séquence à 0 du gourou Oracle Tom Kyte . Grande discussion sur les avantages et les inconvénients dans les liens ci-dessous aussi.

 tkyte@TKYTE901.US.ORACLE.COM> create or replace procedure reset_seq( p_seq_name in varchar2 ) is l_val number; begin execute immediate 'select ' || p_seq_name || '.nextval from dual' INTO l_val; execute immediate 'alter sequence ' || p_seq_name || ' increment by -' || l_val || ' minvalue 0'; execute immediate 'select ' || p_seq_name || '.nextval from dual' INTO l_val; execute immediate 'alter sequence ' || p_seq_name || ' increment by 1 minvalue 0'; end; / 

Depuis cette page: SQL dynamic pour réinitialiser la valeur de séquence
Une autre bonne discussion est également ici: Comment réinitialiser les séquences?

Un vrai redémarrage n’est pas possible. (Corrigez-moi si j’ai tort, s’il-vous plait!).

Toutefois, si vous souhaitez le définir sur 0, vous pouvez simplement le supprimer et le recréer.

Si vous voulez définir une valeur spécifique, vous pouvez définir une valeur négative pour INCREMENT et obtenir la valeur suivante.

C’est-à-dire que si votre séquence est à 500, vous pouvez la régler à 100 via

 ALTER SEQUENCE serial INCREMENT BY -400; SELECT serial.NEXTVAL FROM dual; ALTER SEQUENCE serial INCREMENT BY 1; 

Ceci est mon approche:

  1. laisser tomber la séquence
  2. le recréer

Exemple:

 --Drop sequence DROP SEQUENCE MY_SEQ; -- Create sequence create sequence MY_SEQ minvalue 1 maxvalue 999999999999999999999 start with 1 increment by 1 cache 20; 

Mon approche est une extension de l’exemple de Dougman .

Les extensions sont …

Transmettez la valeur de départ en tant que paramètre. Pourquoi? J’aime appeler la chose réinitialisant la séquence à l’ID max utilisé dans certaines tables . Je finis par appeler ce proc depuis un autre script qui exécute plusieurs appels pour tout un tas de séquences, rétablissant le niveau suivant à un niveau suffisamment élevé pour ne pas provoquer de violation de clé primaire lorsque j’utilise la valeur de la séquence pour un identifiant unique.

Il honore également la minvalue précédente. Il peut en fait pousser la valeur suivante plus haut si la valeur de p_val ou la valeur de minuit existante est supérieure à la valeur actuelle ou calculée suivante.

Mieux encore, il peut être appelé pour réinitialiser à une valeur spécifiée, et attendez que la procédure wrapper “corrige toutes mes séquences” à la fin.

 create or replace procedure Reset_Sequence( p_seq_name in varchar2, p_val in number default 0) is l_current number := 0; l_difference number := 0; l_minvalue user_sequences.min_value%type := 0; begin select min_value into l_minvalue from user_sequences where sequence_name = p_seq_name; execute immediate 'select ' || p_seq_name || '.nextval from dual' INTO l_current; if p_Val < l_minvalue then l_difference := l_minvalue - l_current; else l_difference := p_Val - l_current; end if; if l_difference = 0 then return; end if; execute immediate 'alter sequence ' || p_seq_name || ' increment by ' || l_difference || ' minvalue ' || l_minvalue; execute immediate 'select ' || p_seq_name || '.nextval from dual' INTO l_difference; execute immediate 'alter sequence ' || p_seq_name || ' increment by 1 minvalue ' || l_minvalue; end Reset_Sequence; 

Cette procédure est utile à elle seule, mais maintenant ajoutons-en une autre qui l'appelle et spécifie tout par programmation avec une convention de dénomination de séquence et recherche la valeur maximale utilisée dans une table / un champ existant ...

 create or replace procedure Reset_Sequence_to_Data( p_TableName varchar2, p_FieldName varchar2 ) is l_MaxUsed NUMBER; BEGIN execute immediate 'select coalesce(max(' || p_FieldName || '),0) from '|| p_TableName into l_MaxUsed; Reset_Sequence( p_TableName || '_' || p_Fieldname || '_SEQ', l_MaxUsed ); END Reset_Sequence_to_Data; 

Maintenant, nous cuisinons avec du gaz!

La procédure ci-dessus vérifie la valeur maximale d'un champ dans une table, crée un nom de séquence à partir de la paire table / champ et appelle "Reset_Sequence" avec cette valeur maximale détectée.

La dernière pièce de ce puzzle et la cerise sur le gâteau viennent ensuite ...

 create or replace procedure Reset_All_Sequences is BEGIN Reset_Sequence_to_Data( 'ACTIVITYLOG', 'LOGID' ); Reset_Sequence_to_Data( 'JOBSTATE', 'JOBID' ); Reset_Sequence_to_Data( 'BATCH', 'BATCHID' ); END Reset_All_Sequences; 

Dans ma firebase database actuelle, une centaine d'autres séquences sont réinitialisées via ce mécanisme, il y a donc 97 appels supplémentaires à Reset_Sequence_to_Data dans cette procédure ci-dessus.

Aimer? Le detesté? Indifférent?

 alter sequence serial restart start with 0; 

Cette fonctionnalité est nouvelle dans Oracle 12c. Il n’est pas inclus dans la documentation officielle . Je l’ai trouvé dans des scripts générés par le package Oracle DBMS_METADATA_DIFF .

AVERTISSEMENT: J’ai utilisé cette fonctionnalité à plusieurs resockets sur des systèmes de production et, à mon avis, il est préférable d’utiliser cette commande dans des scripts ad hoc. Mais vous pourriez ne pas vouloir l’inclure dans une procédure dans le cadre d’une application. J’ai créé une demande de service Oracle pour en savoir plus sur cette fonctionnalité. Ce n’est pas simplement un bug de documentation, c’est une fonctionnalité non prise en charge. Il est possible que la commande disparaisse un jour, comme WM_CONCAT . (Bien que je pense que c’est peu probable – la syntaxe Oracle disparaît rarement, et c’est une fonctionnalité simple utilisée en interne à au moins deux endroits.)

Le script suivant définit la séquence sur la valeur souhaitée:

Étant donné une séquence récemment créée nommée PCS_PROJ_KEY_SEQ et la table PCS_PROJ:

 BEGIN DECLARE PROJ_KEY_MAX NUMBER := 0; PROJ_KEY_CURRVAL NUMBER := 0; BEGIN SELECT MAX (PROJ_KEY) INTO PROJ_KEY_MAX FROM PCS_PROJ; EXECUTE IMMEDIATE 'ALTER SEQUENCE PCS_PROJ_KEY_SEQ INCREMENT BY ' || PROJ_KEY_MAX; SELECT PCS_PROJ_KEY_SEQ.NEXTVAL INTO PROJ_KEY_CURRVAL FROM DUAL; EXECUTE IMMEDIATE 'ALTER SEQUENCE PCS_PROJ_KEY_SEQ INCREMENT BY 1'; END; END; / 

Cette procédure stockée redémarre ma séquence:

 Create or Replace Procedure Reset_Sequence is SeqNbr Number; begin /* Reset Sequence 'seqXRef_RowID' to 0 */ Execute Immediate 'Select seqXRef.nextval from dual ' Into SeqNbr; Execute Immediate 'Alter sequence seqXRef increment by - ' || TO_CHAR(SeqNbr) ; Execute Immediate 'Select seqXRef.nextval from dual ' Into SeqNbr; Execute Immediate 'Alter sequence seqXRef increment by 1'; END; 

/

Il existe un autre moyen de réinitialiser une séquence dans Oracle: définissez les propriétés maxvalue et cycle . Lorsque la nextval de la séquence atteint la valeur maxvalue , si la propriété cycle est définie, elle reprendra à partir de la minvalue de la séquence.

L’avantage de cette méthode par rapport à la définition d’un increment by négatif increment by est que la séquence peut continuer à être utilisée pendant le processus de réinitialisation, ce qui réduit les risques d’interruption pour effectuer la réinitialisation.

La valeur de maxvalue doit être supérieure à celle de la nextval , de sorte que la procédure ci-dessous inclut un paramètre facultatif permettant un tampon si la séquence est à nouveau accédée entre la sélection de la nextval de la procédure et la définition de la propriété cycle .

 create sequence s start with 1 increment by 1; select s.nextval from dual connect by level < = 20; NEXTVAL ---------- 1 ... 20 create or replace procedure reset_sequence ( i_buffer in pls_integer default 0) as maxval pls_integer; begin maxval := s.nextval + greatest(i_buffer, 0); --ensure we don't go backwards! execute immediate 'alter sequence s cycle minvalue 0 maxvalue ' || maxval; maxval := s.nextval; execute immediate 'alter sequence s nocycle maxvalue 99999999999999'; end; / show errors exec reset_sequence; select s.nextval from dual; NEXTVAL ---------- 1 

La procédure en cours permet toujours qu'une autre session récupère la valeur 0, ce qui peut ou non être un problème pour vous. Si c'est le cas, vous pouvez toujours:

  • Set minvalue 1 dans le premier alter
  • Exclure le deuxième nextval
  • Déplacez l'instruction pour définir la propriété nocycle dans une autre procédure, à exécuter ultérieurement (en supposant que vous souhaitiez le faire).

1) Supposons que vous créez une SEQUENCE comme ci-dessous:

 CREATE SEQUENCE TESTSEQ INCREMENT BY 1 MINVALUE 1 MAXVALUE 500 NOCACHE NOCYCLE NOORDER 

2) Maintenant, vous récupérez les valeurs de SEQUENCE. Disons que j’ai récupéré quatre fois comme indiqué ci-dessous.

 SELECT TESTSEQ.NEXTVAL FROM dual SELECT TESTSEQ.NEXTVAL FROM dual SELECT TESTSEQ.NEXTVAL FROM dual SELECT TESTSEQ.NEXTVAL FROM dual 

3) Après avoir exécuté les quatre commandes ci-dessus, la valeur de la SEQUENCE sera 4. Maintenant, supposons que je réinitialise la valeur de SEQUENCE à 1. Suivez les étapes suivantes. Suivez toutes les étapes dans le même ordre que ci-dessous:

  1. ALTER SEQUENCE TESTSEQ INCREMENT BY -3;
  2. SELECT TESTSEQ.NEXTVAL FROM dual
  3. ALTER SEQUENCE TESTSEQ INCREMENT BY 1;
  4. SELECT TESTSEQ.NEXTVAL FROM dual

Modifier la valeur INCREMENT de la séquence, l’incrémenter puis la modifier est très indolore. De plus, vous avez l’avantage de ne pas avoir à rétablir toutes les subventions comme vous l’auriez fait si vous aviez abandonné / recréé la séquence.

Je crée un bloc pour réinitialiser toutes mes séquences:

 DECLARE I_val number; BEGIN FOR US IN (SELECT US.SEQUENCE_NAME FROM USER_SEQUENCES US) LOOP execute immediate 'select ' || US.SEQUENCE_NAME || '.nextval from dual' INTO l_val; execute immediate 'alter sequence ' || US.SEQUENCE_NAME || ' increment by -' || l_val || ' minvalue 0'; execute immediate 'select ' || US.SEQUENCE_NAME || '.nextval from dual' INTO l_val; execute immediate 'alter sequence ' || US.SEQUENCE_NAME || ' increment by 1 minvalue 0'; END LOOP; END; 

Voici une procédure plus robuste pour modifier la prochaine valeur renvoyée par une séquence, et bien plus encore.

  • Tout d’abord, il protège contre les attaques par injection SQL, car aucune des chaînes transmises n’est utilisée pour créer directement l’une des instructions SQL dynamics,
  • Deuxièmement, il empêche la prochaine valeur de séquence d’être définie en dehors des limites des valeurs de séquence min ou max. La next_value sera! = min_value et entre min_value et max_value .
  • Troisièmement, il prend en compte le paramètre increment_by actuel (ou proposé) ainsi que tous les autres parameters de séquence lors du nettoyage.
  • Quasortingèmement, tous les parameters, à l’exception du premier, sont facultatifs et, sauf spécification contraire, prennent le paramètre de séquence actuel comme valeur par défaut. Si aucun paramètre facultatif n’est spécifié, aucune action n’est effectuée.
  • Enfin, si vous essayez de modifier une séquence qui n’existe pas (ou qui n’appartient pas à l’utilisateur actuel), elle ORA-01403: no data found un ORA-01403: no data found erreur ORA-01403: no data found .

Voici le code:

 CREATE OR REPLACE PROCEDURE alter_sequence( seq_name user_sequences.sequence_name%TYPE , next_value user_sequences.last_number%TYPE := null , increment_by user_sequences.increment_by%TYPE := null , min_value user_sequences.min_value%TYPE := null , max_value user_sequences.max_value%TYPE := null , cycle_flag user_sequences.cycle_flag%TYPE := null , cache_size user_sequences.cache_size%TYPE := null , order_flag user_sequences.order_flag%TYPE := null) AUTHID CURRENT_USER AS l_seq user_sequences%rowtype; l_old_cache user_sequences.cache_size%TYPE; l_next user_sequences.min_value%TYPE; BEGIN -- Get current sequence settings as defaults SELECT * INTO l_seq FROM user_sequences WHERE sequence_name = seq_name; -- Update target settings l_old_cache := l_seq.cache_size; l_seq.increment_by := nvl(increment_by, l_seq.increment_by); l_seq.min_value := nvl(min_value, l_seq.min_value); l_seq.max_value := nvl(max_value, l_seq.max_value); l_seq.cycle_flag := nvl(cycle_flag, l_seq.cycle_flag); l_seq.cache_size := nvl(cache_size, l_seq.cache_size); l_seq.order_flag := nvl(order_flag, l_seq.order_flag); IF next_value is NOT NULL THEN -- Determine next value without exceeding limits l_next := LEAST(GREATEST(next_value, l_seq.min_value+1),l_seq.max_value); -- Grab the actual latest seq number EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_seq.sequence_name || ' INCREMENT BY 1' || ' MINVALUE '||least(l_seq.min_value,l_seq.last_number-l_old_cache) || ' MAXVALUE '||greatest(l_seq.max_value,l_seq.last_number) || ' NOCACHE' || ' ORDER'; EXECUTE IMMEDIATE 'SELECT '||l_seq.sequence_name||'.NEXTVAL FROM DUAL' INTO l_seq.last_number; l_next := l_next-l_seq.last_number-1; -- Reset the sequence number IF l_next <> 0 THEN EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_seq.sequence_name || ' INCREMENT BY '||l_next || ' MINVALUE '||least(l_seq.min_value,l_seq.last_number) || ' MAXVALUE '||greatest(l_seq.max_value,l_seq.last_number) || ' NOCACHE' || ' ORDER'; EXECUTE IMMEDIATE 'SELECT '||l_seq.sequence_name||'.NEXTVAL FROM DUAL' INTO l_next; END IF; END IF; -- Prepare Sequence for next use. IF COALESCE( cycle_flag , next_value , increment_by , min_value , max_value , cache_size , order_flag) IS NOT NULL THEN EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_seq.sequence_name || ' INCREMENT BY '||l_seq.increment_by || ' MINVALUE '||l_seq.min_value || ' MAXVALUE '||l_seq.max_value || CASE l_seq.cycle_flag WHEN 'Y' THEN ' CYCLE' ELSE ' NOCYCLE' END || CASE l_seq.cache_size WHEN 0 THEN ' NOCACHE' ELSE ' CACHE '||l_seq.cache_size END || CASE l_seq.order_flag WHEN 'Y' THEN ' ORDER' ELSE ' NOORDER' END; END IF; END; 

Dans mon projet, une fois que quelqu’un a saisi manuellement les enregistrements sans utiliser de séquence, je dois réinitialiser manuellement la valeur de séquence, pour laquelle j’ai écrit ci-dessous l’extrait de code SQL:

 declare max_db_value number(10,0); cur_seq_value number(10,0); counter number(10,0); difference number(10,0); dummy_number number(10); begin -- enter table name here select max(id) into max_db_value from persons; -- enter sequence name here select last_number into cur_seq_value from user_sequences where sequence_name = 'SEQ_PERSONS'; difference := max_db_value - cur_seq_value; for counter in 1..difference loop -- change sequence name here as well select SEQ_PERSONS.nextval into dummy_number from dual; end loop; end; 

Veuillez noter que le code ci-dessus fonctionnera si la séquence est en retard.

Jezus, toute cette programmation pour juste un redémarrage de l’index … Peut-être que je suis un idiot, mais pour le pré-oracle 12 (qui a une fonction de redémarrage), qu’est-ce qui ne va pas avec un simpel:

 drop sequence blah; create sequence blah 

?

Voici comment faire en sorte que toutes les séquences à incrémentation automatique correspondent aux données réelles:

  1. Créez une procédure pour appliquer la valeur suivante, comme cela a déjà été décrit dans cette discussion:

     CREATE OR REPLACE PROCEDURE Reset_Sequence( P_Seq_Name IN VARCHAR2, P_Val IN NUMBER DEFAULT 0) IS L_Current NUMBER := 0; L_Difference NUMBER := 0; L_Minvalue User_Sequences.Min_Value%Type := 0; BEGIN SELECT Min_Value INTO L_Minvalue FROM User_Sequences WHERE Sequence_Name = P_Seq_Name; EXECUTE Immediate 'select ' || P_Seq_Name || '.nextval from dual' INTO L_Current; IF P_Val < L_Minvalue THEN L_Difference := L_Minvalue - L_Current; ELSE L_Difference := P_Val - L_Current; END IF; IF L_Difference = 0 THEN RETURN; END IF; EXECUTE Immediate 'alter sequence ' || P_Seq_Name || ' increment by ' || L_Difference || ' minvalue ' || L_Minvalue; EXECUTE Immediate 'select ' || P_Seq_Name || '.nextval from dual' INTO L_Difference; EXECUTE Immediate 'alter sequence ' || P_Seq_Name || ' increment by 1 minvalue ' || L_Minvalue; END Reset_Sequence; 
  2. Créez une autre procédure pour réconcilier toutes les séquences avec le contenu réel:

     CREATE OR REPLACE PROCEDURE RESET_USER_SEQUENCES_TO_DATA IS STMT CLOB; BEGIN SELECT 'select ''BEGIN'' || chr(10) || x || chr(10) || ''END;'' FROM (select listagg(x, chr(10)) within group (order by null) x FROM (' || X || '))' INTO STMT FROM (SELECT LISTAGG(X, ' union ') WITHIN GROUP ( ORDER BY NULL) X FROM (SELECT CHR(10) || 'select ''Reset_Sequence(''''' || SEQ_NAME || ''''','' || coalesce(max(' || COL_NAME || '), 0) || '');'' x from ' || TABLE_NAME X FROM (SELECT TABLE_NAME, REGEXP_SUBSTR(WTEXT, 'NEW\.(\S*) IS NULL',1,1,'i',1) COL_NAME, REGEXP_SUBSTR(BTEXT, '(\.|\s)([a-z_]*)\.nextval',1,1,'i',2) SEQ_NAME FROM USER_TRIGGERS LEFT JOIN (SELECT NAME BNAME, TEXT BTEXT FROM USER_SOURCE WHERE TYPE = 'TRIGGER' AND UPPER(TEXT) LIKE '%NEXTVAL%' ) ON BNAME = TRIGGER_NAME LEFT JOIN (SELECT NAME WNAME, TEXT WTEXT FROM USER_SOURCE WHERE TYPE = 'TRIGGER' AND UPPER(TEXT) LIKE '%IS NULL%' ) ON WNAME = TRIGGER_NAME WHERE TRIGGER_TYPE = 'BEFORE EACH ROW' AND TRIGGERING_EVENT = 'INSERT' ) ) ) ; EXECUTE IMMEDIATE STMT INTO STMT; --dbms_output.put_line(stmt); EXECUTE IMMEDIATE STMT; END RESET_USER_SEQUENCES_TO_DATA; 

REMARQUES:

  1. La procédure extrait les noms du code de déclenchement et ne dépend pas des conventions de dénomination
  2. Pour vérifier le code généré avant l'exécution, changez les commentaires sur les deux dernières lignes

Vous pouvez utiliser l’option CYCLE, illustrée ci-dessous:

 CREATE SEQUENCE test_seq MINVALUE 0 MAXVALUE 100 START WITH 0 INCREMENT BY 1 CYCLE; 

Dans ce cas, lorsque la séquence atteindra MAXVALUE (100), elle sera recyclée vers MINVALUE (0).

Dans le cas d’une séquence décrémentée, la séquence serait recyclée dans le MAXVALUE.

Je fais une alternative que l’utilisateur n’a pas besoin de connaître les valeurs, le système obtient et utilise des variables pour mettre à jour.

 --Atualizando sequence da tabela SIGA_TRANSACAO, pois está desatualizada DECLARE actual_sequence_number INTEGER; max_number_from_table INTEGER; difference INTEGER; BEGIN SELECT [nome_da_sequence].nextval INTO actual_sequence_number FROM DUAL; SELECT MAX([nome_da_coluna]) INTO max_number_from_table FROM [nome_da_tabela]; SELECT (max_number_from_table-actual_sequence_number) INTO difference FROM DUAL; IF difference > 0 then EXECUTE IMMEDIATE CONCAT('alter sequence [nome_da_sequence] increment by ', difference); --aqui ele puxa o próximo valor usando o incremento necessário SELECT [nome_da_sequence].nextval INTO actual_sequence_number from dual; --aqui volta o incremento para 1, para que futuras inserções funcionem normalmente EXECUTE IMMEDIATE 'ALTER SEQUENCE [nome_da_sequence] INCREMENT by 1'; DBMS_OUTPUT.put_line ('A sequence [nome_da_sequence] foi atualizada.'); ELSE DBMS_OUTPUT.put_line ('A sequence [nome_da_sequence] NÃO foi atualizada, já estava OK!'); END IF; END;