Let's say that we have a sequence 'my_sequence' (pretty original huh?) whose nextval is 900000:
select my_sequence.nextval from dual; > 900000
However we discover that after deleting some data from a table, we realize that we need to reset the sequence so that the next value is 400000. This can be done in 3 steps:
- Increment the sequence by a negative number which is the difference between the current value and the new value (900001 - 400000)
- Get the next value from dual
- Re-adjust the increment of the sequence back to 1.
In SQL this can be done as follows:
alter sequence my_sequence increment by -500001; select my_sequence.nextval from dual; alter sequence my_sequence increment by 1;