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

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

In short, game it:

-- Current sequence value is 1000ALTER SEQUENCE x INCREMENT BY -999;Sequence altered.SELECT X.NEXTVAL FROM DUAL;1ALTER 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.

Declare  difference INTEGER;  sqlstmt varchar2(255);  sequenceValue Number;beginsqlstmt := 'ALTER SEQUENCE YOURSEQUENCE INCREMENT BY ';select YOURSEQUENCE.NEXTVAL into sequenceValue from dual;select  (nvl(Max(YOURID),0) - sequenceValue)+1 into difference from YOURTABLE;if difference > 0 then  EXECUTE IMMEDIATE sqlstmt || difference;  select  YOURSEQUENCE.NEXTVAL INTO sequenceValue from dual;  EXECUTE IMMEDIATE sqlstmt || 1;end if;end;