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

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.

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;

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; it is apparently intended for internal Oracle use.

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;

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...

With oracle 10.2g:

select  level, sequence.NEXTVAL
from  dual 
connect by level <= (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.

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);

In my case I used an approach to reset sequence to zero and than setting from zero to max of target table:

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;

Apologies for not having a one-liner solution, since my program runs in Typeorm with node-oracle. However, I think the following SQL commands would help with this problem.

Get the LAST_NUMBER from your sequence.

SELECT SEQUENCE_NAME, LAST_NUMBER FROM ALL_SEQUENCES WHERE SEQUENCE_NAME = '${sequenceName}'

Get the value of your PK from the last row (in this case ID is the PK).

SELECT ID FROM ${tableName} ORDER BY ID DESC FETCH NEXT 1 ROWS ONLY

Lastly update LAST_NUMBER to the value + 1:

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