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

Question

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 1

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

Answer 2

In short, game it:

-- Current sequence value is 1000

ALTER SEQUENCE x INCREMENT BY -999;
Sequence altered.

SELECT X.NEXTVAL FROM DUAL;
1

ALTER SEQUENCE x INCREMENT BY 1;
Sequence altered.

You can get the max sequence value used within your table, do the math, and update the sequence accordingly.