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?
17 réponses
Voici une bonne procédure pour réinitialiser n'importe quelle séquence à 0 depuis Oracle guru Tom Kyte . Grande discussion sur les avantages et les inconvénients dans les liens ci-dessous.
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;
/
de cette page: SQL dynamique pour réinitialiser la valeur de séquence
Une autre bonne discussion est aussi ici: comment réinitialiser les séquences?
un vrai redémarrage n'est pas possible AFAIK . (Corrigez-moi si je me trompe!).
cependant, si vous voulez le mettre à 0, Vous pouvez simplement le supprimer et le recréer.
Si vous souhaitez définir une valeur spécifique, vous pouvez définir l'INCRÉMENT d'une valeur négative et d'obtenir la valeur suivante.
C'est-à-dire, 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;
C'est mon approche:
- abandonner la séquence
- 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 un teensy extension Dougman l'exemple de .
Les Extensionssont...
passer dans la valeur de graine comme paramètre. Pourquoi? J'aime appeler la chose réinitialiser la séquence de retour à L'ID max utilisé dans une certaine table . Je finis par appeler ce proc à partir d'un autre script qui exécute plusieurs appels pour un tas de séquences, réinitialisant nextval à un niveau élevé assez pour ne pas causer de violations de clé primaire quand j'utilise la valeur de la séquence pour un identifiant unique.
il honore également le précédent minvalue . Il peut en effet pousser la valeur suivante encore plus haut si les p_val ou MinValue existante désirés sont plus élevés que la valeur actuelle ou calculée suivante.
mieux que tout, il peut être appelé à réinitialiser à un valeur spécifiée, et attendez de voir le wrapper "corriger toutes mes séquences" procédure à 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 par elle-même, mais maintenant ajoutons une autre qui l'appelle et spécifie tout programmatiquement avec une convention de nommage de séquence et en recherchant la valeur maximale utilisée dans une table/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 on cuisine avec du gaz!
La procédure ci-dessus seront vérifiez la valeur max d'un champ dans une table, construit un nom de séquence à partir de la paire table/champ et invoque "Reset_Sequence" avec cette valeur max détectée.
La dernière pièce de ce puzzle, et la cerise sur le gâteau vient 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 base de données actuelle il y a environ une centaine d'autres séquences étant réinitialisées par ce mécanisme, il y a donc 97 autres appels à Reset_Sequence_to_Data dans la procédure que ci-dessus.
vous aimez? Il déteste? Indifférent?
alter sequence serial restart start with 0;
cette fonctionnalité est nouvelle dans Oracle 12c. Il s'agit de et non inclus dans la documentation officielle . Je l'ai trouvé dans les scripts générés par le paquet Oracle DBMS_METADATA_DIFF .
WARNING: j'ai utilisé cette fonctionnalité plusieurs fois sur des systèmes de production et à mon avis, il est correct d'utiliser cette commande dans des scripts ad-hoc. Mais vous pourriez ne pas vouloir l'inclure dans une procédure faisant partie d'une demande. J'ai créé une demande de Service Oracle à propos de cette fonctionnalité; ce n'est pas juste un bogue de documentation, c'est une fonctionnalité non supportée. 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 dans au moins deux endroits.)
le script suivant définit la séquence à une valeur désirée:
étant donné une séquence fraîchement créée appelée PCS_PROJ_KEY_SEQ et 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;
/
Ce "151920920 des" procédures stockées 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 y a une autre façon de réinitialiser une séquence dans Oracle: définir les propriétés maxvalue
et cycle
. Lorsque la nextval
de la séquence frappe la maxvalue
, si la propriété cycle
est définie, alors elle recommencera à partir de la minvalue
de la séquence.
l'avantage de cette méthode par rapport au réglage d'un négatif increment by
est la séquence peut continuer à être utilisé pendant que le processus de réinitialisation fonctionne, réduisant la chance que vous avez besoin de prendre certains forme de l'arrêt pour faire la réinitialisation.
la valeur pour maxvalue
doit être plus grande que la valeur actuelle nextval
, ainsi la procédure ci-dessous inclut un paramètre optionnel permettant un tampon dans le cas où la séquence est à nouveau accessible entre la sélection de la nextval
dans 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 l'état permet la possibilité qu'une autre session atteigne la valeur 0, qui peut ou peut-être pas un problème pour vous. Si c'est le cas, vous pouvez toujours:
- Set
minvalue 1
dans la première modifier - Exclure la seconde
nextval
fetch - déplacez la déclaration pour définir la propriété
nocycle
dans une autre procédure, qui sera exécutée à une date ultérieure (en supposant que vous voulez faire ceci).
1) supposons que vous créez une séquence comme suit:
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 séquence sera 4. Supposons maintenant que j'ai réinitialisé la valeur de la séquence à 1. Suivez les étapes suivantes. Suivre toutes les étapes dans le même ordre comme indiqué ci-dessous:
-
ALTER SEQUENCE TESTSEQ INCREMENT BY -3;
-
SELECT TESTSEQ.NEXTVAL FROM dual
-
ALTER SEQUENCE TESTSEQ INCREMENT BY 1;
-
SELECT TESTSEQ.NEXTVAL FROM dual
modifier la valeur incrémentielle de la séquence, l'incrémenter, puis la modifier en arrière est assez indolore, plus vous avez l'avantage supplémentaire de ne pas avoir à rétablir toutes les subventions que vous auriez si vous aviez laissé tomber/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 valeur suivante retournée par une séquence, plus beaucoup plus.
- tout d'abord, il protège contre les attaques par injection SQL car aucune des chaînes passées ne sont utilisées pour créer directement l'un des énoncés SQL dynamiques,
- Deuxième il empêche la prochaine valeur de la séquence de jeu à l'extérieur des limites de la min ou max des valeurs de séquence. Le
next_value
le sera !=min_value
et entremin_value
etmax_value
. - Troisième il prend le courant (ou proposé)
increment_by
, définissant ainsi que tous les autres paramètres de la séquence en compte lors du nettoyage. - quatrième tous les paramètres, sauf le PREMIER, sont optionnels et, sauf indication contraire, prennent par défaut la séquence actuelle. Si aucun paramètre optionnel n'est spécifié, aucune mesure n'est prise.
- enfin si vous essayez de modifier une séquence qui n'existe pas (ou n'est pas la propriété de l'utilisateur courant) il va soulever une 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 qu'il est arrivé que quelqu'un a entré manuellement les enregistrements sans utiliser la séquence, donc je dois réinitialiser la valeur de séquence manuellement, pour lequel j'ai écrit ci-dessous code sql snippet:
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 un simple redémarrage de l'index... Peut-être que je suis un idiot, mais pour pre-oracle 12 (qui a une fonction de redémarrage), ce qui ne va pas avec un simpel:
drop sequence blah;
create sequence blah
?
vous pouvez utiliser L'option CYCLE, indiqué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 atteint MAXVALUE (100), elle se recycle en MINVALUE (0).
dans le cas d'une séquence décrémentée, la séquence serait recyclée à la valeur max.
Voici comment faire correspondre toutes les séquences d'incréments automatiques aux données réelles:
-
créez une procédure pour appliquer la valeur suivante comme cela a déjà été décrit dans ce thread:
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;
-
créer 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;
NOTES:
- extraits de procédure noms de déclenchement de code et ne dépend pas de conventions de nommage
- pour vérifier le code généré avant l'exécution, commente les deux dernières lignes
je fais une alternative que l'utilisateur n'a pas besoin de connaître les valeurs, le système obtenir et utiliser 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;