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?
+4
A:
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.
David Andres
2009-09-15 11:44:37
+3
A:
If ID is the name of your PK column and PK_SEQ is the name of your sequence:
Find the value of the highest PK by SELECT MAX(ID) FROM tableName
Find the value of the next PK_SEQ by SELECT PK_SEQ.NEXTVAL FROM DUAL
- If #2 > #1 then nothing needs to be done, assuming you treat these values as true surrogate keys
- Otherwise, alter the sequence to jump to the max ID by ALTER SEQUENCE PK_SEQ INCREMENT BY [#1 value - #2 value]
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
This all assumes that you don't have new inserts into the table while you're doing this...
dpbradley
2009-09-15 12:01:55
I'm struggling with how to do step 3, I've tried various different syntaxes but cant get it to work
AJM
2009-10-21 11:39:48
The intent of step 3 is just to compare the largest PK value to the next sequence value. So, for example, if the SELECT from step 1 resulted in a result of 100, and the SELECT from step 2 resulted in a result of 90 that means that you have "jump" 11 sequences. When you alter the sequence in step 4, the SELECT in step 5 will move the sequence 10 values, to 100. After the increment is reset in step 6, the next "SELECT PK_SEQ.NEXTVAL FROM DUAL" will give you 101.
dpbradley
2009-10-21 11:54:49
In step 4 I can't get the syntax to do a subtraction in the Increment by clause. I've tried [a-b] a-b and selecr a-b from dual, but not with any success.
AJM
2009-10-26 12:16:05
Sorry - it looks like I wasn't clear - you have to determine the value (call it N) and then use the statement "ALTER SEQUENCE PK_SEQ INCREMENT BY N" - I was using the syntax I provided as sort of pseudo code.
dpbradley
2009-10-26 12:27:00
I had tried that approach but the problem was a syntaticall issue whereby I was foolishly not appending my variable proparly e.g.EXECUTE IMMEDIATE 'ALTER SEQUENCE NEXT_VALIDATED_TABLE_ID INCREMENT BY ' || N ;
AJM
2009-10-26 13:42:08
PS thanks for the help
AJM
2009-10-26 13:43:12
+1
A:
In some cases, you may find it easier to simply get the current max value and then
drop sequence x;
create sequence x start with {current max + 1};
The app will be broken after you do the drop. But that will keep anybody from inserting rows during that period, and creating a sequence is quick. Make sure you recreate any grants on the sequence since those will be dropped when the sequence is. And you may want to manually recompile any plsql that depends on the sequence.
Jim Hudson
2009-09-15 13:15:11
@Jim - I would avoid dropping any db object if there is an alternative. Dropping the sequence will not necessarily prevent inserts on the table that do not reference the sequence. As you point out you also have the extra work of capturing the grants and recompiling dependent objects.
dpbradley
2009-09-15 13:41:13