Oracle 9 - Resetting Sequence to match the state of the table

oracle sequence oracle9i
AJM picture AJM · Sep 15, 2009 · Viewed 26.9k times · Source

I have a sequence used to seed my (Integer based) primary keys in an oracle table.

It appears this sequence has not always been used to insert new values into the table. How do I get the sequence back in step with the actual values in the table?

Answer

dpbradley picture dpbradley · Sep 15, 2009

If ID is the name of your PK column and PK_SEQ is the name of your sequence:

  1. Find the value of the highest PK by SELECT MAX(ID) FROM tableName

  2. Find the value of the next PK_SEQ by SELECT PK_SEQ.NEXTVAL FROM DUAL

  3. If #2 > #1 then nothing needs to be done, assuming you treat these values as true surrogate keys
  4. Otherwise, alter the sequence to jump to the max ID by ALTER SEQUENCE PK_SEQ INCREMENT BY [#1 value - #2 value]

  5. Bump the sequence by SELECT PK_SEQ.NEXTVAL FROM DUAL

  6. Reset the sequence increment value to 1 by ALTER SEQUENCE PK_SEQ INCREMENT BY 1

This all assumes that you don't have new inserts into the table while you're doing this...

Related questions

Create a Sequence with START WITH from Query

How can I create a Sequence where my START WITH value comes from a query? I'm trying this way: CREATE SEQUENCE "Seq" INCREMENT BY 1 START WITH (SELECT MAX("ID") FROM "Table"); But, I get the ORA-01722 error

Read More
Table,View Or Sequence reference 'SEQUENCE.NEXTVAL' not allowed in this context

I'm having this error while trying to compile the following trigger : CREATE OR REPLACE TRIGGER INCREMENTER_ID_CONSISTANCE BEFORE INSERT ON BD.CONSISTANCE for each row BEGIN :new.code := ID_CONSISTANCE.nextval; END; **ERROR** : Table,View Or Sequence reference 'ID_…

Read More
How do I reset a sequence in Oracle?

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

Read More