How do I reset a sequence in Oracle

Question

In PostgreSQL  I can do something like this   ALTER SEQUENCE serial RESTART WITH 0    Is there an Oracle equivalent

User · Answer

There is another way to reset a sequence in Oracle: set the maxvalue and cycle properties. When the nextval of the sequence hits the maxvalue, if the cycle property is set then it will begin again from the minvalue of the sequence.

The advantage of this method compared to setting a negative increment by is the sequence can continue to be used while the reset process runs, reducing the chance you need to take some form of outage to do the reset.

The value for maxvalue has to be greater than the current nextval, so the procedure below includes an optional parameter allowing a buffer in case the sequence is accessed again between selecting the nextval in the procedure and setting the cycle property.

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

The procedure as stands still allows the possibility that another session will fetch the value 0, which may or may not be an issue for you. If it is, you could always:

Set minvalue 1 in the first alter
Exclude the second nextval fetch
Move the statement to set the nocycle property into another procedure, to be run at a later date (assuming you want to do this).

User · Answer

alter sequence serial restart start with 1   This feature was officially added in 18c but is unofficially available since 12 1  It is arguably safe to use this undocumented feature in 12 1   Even though the syntax is not included in the official documentation  it is generated by the Oracle package DBMS METADATA DIFF   I ve used it several times on production systems   However  I created an Oracle Service request and they verified that it s not a documentation bug  the feature is truly unsupported  In 18c  the feature does not appear in the SQL Language Syntax  but is included in the Database Administrator s Guide

User · Answer

I create a block to reset all my sequences   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

User · Answer

Here is a good procedure for resetting any sequence to 0 from Oracle guru Tom Kyte   Great discussion on the pros and cons in the links below too   tkyte TKYTE901 US ORACLE COM gt   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      From this page  Dynamic SQL to reset sequence value Another good discussion is also here  How to reset sequences

User · Answer

This stored procedure restarts my sequence   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

User · Answer

Stored procedure that worked for me  create or replace procedure reset sequence  p seq name in varchar2  tablename in varchar2   is     l val number      maxvalueid number  begin     execute immediate  select      p seq name      nextval from dual  INTO l val      execute immediate  select max id  from      tablename INTO maxvalueid      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     maxvalueid     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    How to use the stored procedure   execute reset sequence  company sequence   company

User · Answer

Altering the sequence s INCREMENT value  incrementing it  and then altering it back is pretty painless  plus you have the added benefit of not having to re-establish all of the grants as you would had you dropped recreated the sequence

User · Answer

I make an alternative that the user don   t need to know the values  the system get and use variables to update   --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  gt  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

User · Answer

The following script set the sequence to a desired value   Given a freshly created sequence named PCS PROJ KEY SEQ and 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

User · Answer

You can use the CYCLE option  shown below   CREATE SEQUENCE test seq MINVALUE 0 MAXVALUE 100 START WITH 0 INCREMENT BY 1 CYCLE    In this case  when the sequence reaches MAXVALUE  100   it will recycle to the MINVALUE  0    In the case of a decremented sequence  the sequence would recycle to the MAXVALUE

User · Answer

1  Suppose you create a SEQUENCE like shown below   CREATE SEQUENCE TESTSEQ INCREMENT BY 1 MINVALUE 1 MAXVALUE 500 NOCACHE NOCYCLE NOORDER   2  Now you fetch values from SEQUENCE  Lets say I have fetched four times as shown below   SELECT TESTSEQ NEXTVAL FROM dual SELECT TESTSEQ NEXTVAL FROM dual SELECT TESTSEQ NEXTVAL FROM dual SELECT TESTSEQ NEXTVAL FROM dual   3  After executing above four commands the value of the SEQUENCE will be 4  Now suppose I have reset the value of the SEQUENCE to 1 again  The follow the following steps  Follow all the steps in the same order as shown below    ALTER SEQUENCE TESTSEQ INCREMENT BY -3  SELECT TESTSEQ NEXTVAL FROM dual ALTER SEQUENCE TESTSEQ INCREMENT BY 1  SELECT TESTSEQ NEXTVAL FROM dual

User · Answer

A true restart is not possible AFAIK   Please correct me if I m wrong     However  if you want to set it to 0  you can just delete and recreate it   If you want to set it to a specific value  you can set the INCREMENT to a negative value and get the next value   That is  if your sequence is at 500  you can set it to 100 via  ALTER SEQUENCE serial INCREMENT BY -400  SELECT serial NEXTVAL FROM dual  ALTER SEQUENCE serial INCREMENT BY 1

User · Answer

Here s a more robust procedure for altering the next value returned by a sequence  plus a whole lot more    First off it protects against SQL injection attacks since none of the strings passed in are used to directly create any of the dynamic SQL statements  Second it prevents the next sequence value from being set outside the bounds of the min or max sequence values   The next value will be    min value and between min value and max value  Third it takes the current  or proposed  increment by setting as well as all the other sequence settings into account when cleaning up  Fourth all parameters except the first are optional and unless specified take on the current sequence setting as defaults   If no optional parameters are specified no action is taken  Finally if you try altering a sequence that doesn t exist  or is not owned by the current user  it will raise an ORA-01403  no data found error    Here s the 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  lt  gt  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

User · Answer

Here s how to make all auto-increment sequences match actual data    Create a procedure to enforce next value as was already described in this 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         lt  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   Create another procedure to reconcile all sequences with actual content   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    Procedure extracts names from trigger code and does not depend on naming conventions To check generated code before execution  switch comments on last two lines

User · Answer

Jezus  all this programming for just an index restart    Perhaps I m an idiot  but for pre-oracle 12  which has a restart feature   what is wrong with a simpel   drop sequence blah  create sequence blah

User · Answer

In my project  once it happened that someone manually entered the records without using sequence  hence I have to reset sequence value manually  for which I wrote below sql code 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    Please note  the above code will work if the sequence is lagging

User · Answer

My approach is a teensy extension to Dougman s example   Extensions are     Pass in the seed value as a parameter   Why   I like to call the thing resetting the sequence back to the max ID used in some table   I end up calling this proc from another script which executes multiple calls for a whole bunch of sequences  resetting nextval back down to some level which is high enough to not cause primary key violations where I m using the sequence s value for a unique identifier   It also honors the previous minvalue   It may in fact push the next value ever higher if the desired p val or existing minvalue are higher than the current or calculated next value   Best of all  it can be called to reset to a specified value  and just wait until you see the wrapper  fix all my sequences  procedure at the end   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  lt  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    That procedure is useful all by itself  but now let s add another one which calls it and specifies everything programmatically with a sequence naming convention and looking for the maximum value used in an existing table field     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    Now we re cooking with gas     The procedure above will check for a field s max value in a table  builds a sequence name from the table field pair and invokes  Reset Sequence  with that sensed max value   The final piece in this puzzle and the icing on the cake comes next     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    In my actual database there are around one hundred other sequences being reset through this mechanism  so there are 97 more calls to Reset Sequence to Data in that procedure above   Love it   Hate it   Indifferent

User · Answer

This is my approach    drop the sequence recreate it   Example   --Drop sequence  DROP SEQUENCE MY SEQ   -- Create sequence   create sequence MY SEQ minvalue 1 maxvalue 999999999999999999999 start with 1 increment by 1 cache 20

[sql] How do I reset a sequence in Oracle?

Examples related to sql

Examples related to database

Examples related to oracle

Examples related to sequence