Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

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?

like image 589
AJM Avatar asked Sep 15 '09 11:09

AJM


People also ask

How do you change the current value of a sequence in Oracle?

Best AnswerMake the sequence INCREMENT BY that amount. Issue one new number. Change the INCREMENT BY back to the original amount. The next number genereate after that will be the desired value.

How do I bump up a sequence in Oracle?

Bump the sequence by SELECT PK_SEQ. NEXTVAL FROM DUAL. Reset the sequence increment value to 1 by ALTER SEQUENCE PK_SEQ INCREMENT BY 1.


2 Answers

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

like image 120
dpbradley Avatar answered Oct 01 '22 23:10

dpbradley


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.

like image 38
David Andres Avatar answered Oct 02 '22 00:10

David Andres