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

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;

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;

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.