Best way to reset an Oracle sequence to the next value in an existing column

Question

For some reason  people in the past have inserted data without using sequence NEXTVAL   So when I go to use sequence NEXTVAL in order to populate a table  I get a PK violation  since that number is already in use in the table   How can I update the next value so that it is usable   Right now  I m just inserting over and over until it s successful  INSERT INTO tbl  pk  VALUES  sequence NEXTVAL    and that syncs up the nextval

User · Answer

If you can count on having a period of time where the table is in a stable state with no new inserts going on, this should do it (untested):

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;

This enables you to get the sequence back in sync with the table, without dropping/recreating/re-granting the sequence. It also uses no DDL, so no implicit commits are performed. Of course, you're going to have to hunt down and slap the folks who insist on not using the sequence to populate the column...

User · Answer

Today  in Oracle 12c or newer  you probably have the column defined as GENERATED     AS IDENTITY  and Oracle takes care of the sequence itself   You can use an ALTER TABLE Statement to modify  START WITH  of the identity   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

User · Answer

In my case I have a sequence called PS LOG SEQ which had a LAST NUMBER   3920  I then imported some data from PROD to my local machine and inserted into the PS LOG table  Production data had more than 20000 rows with the latest LOG ID  primary key  being 20070  After importing I tried to insert new rows in this table but when saving I got an exception like this one  ORA-00001  unique constraint  LOG PS LOG PK  violated  Surely this has to do with the Sequence PS LOG SEQ associated with the PS LOG table  The LAST NUMBER was colliding with data I imported which had already used the next ID value from the PS LOG SEQ  To solve that I used this command to update the sequence to the latest   max LOG ID    1  alter sequence PS LOG SEQ restart start with 20071   This command reset the LAST NUMBER value and I could then insert new rows into the table  No more collision     Note  this alter sequence command is new in Oracle 12c  Note  this blog post documents the ALTER SEQUENCE RESTART option does exist  but as of 18c  is not documented   Its apparently intended for internal Oracle use

User · Answer

With oracle 10 2g   select  level  sequence NEXTVAL from  dual  connect by level  lt    select max pk  from tbl     will set the current sequence value to the max pk  of your table  i e  the next call to NEXTVAL will give you the right result   if you use Toad  press F5 to run the statement  not F9  which pages the output  thus stopping the increment after  usually  500 rows   Good side  this solution is only DML  not DDL  Only SQL and no PL-SQL  Bad side   this solution prints max pk  rows of output  i e  is usually slower than the ALTER SEQUENCE solution

User · Answer

You can temporarily increase the cache size and do one dummy select and then reset the cache size back to 1  So for example  ALTER SEQUENCE mysequence INCREMENT BY 100   select mysequence nextval from dual   ALTER SEQUENCE mysequence INCREMENT BY 1

User · Answer

These two procedures let me reset the sequence and reset the sequence based on data in a table  apologies for the coding conventions used by this 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

[sql] Best way to reset an Oracle sequence to the next value in an existing column?

Examples related to sql

Examples related to oracle

Examples related to oracle10g

Examples related to primary-key

Examples related to sequence