1

I imported some data into my schema and I have one sequence for all my tables. In my old app, the sequence was around 1000, but this new sequence is starting at 4, and thus I am getting conflicts when trying to enter a new row. Is there a way to update the sequence or is there a way to set the sequence to a certain number during db creation? I don't mind doing a create-drop and importing the data again. I basically just want to set the sequence to start at 1000. Thanks.

Universitas
  • 493
  • 2
  • 5
  • 21

2 Answers2

3

You should be able to increase the increment, access the sequence and then reset the increment, e.g.:

alter sequence [sequence name]
increment by [desired value minus current value];

select [sequence name].nextval from dual;

alter sequence [sequence name]
increment by 1;

I got this syntax from: http://www.techonthenet.com/oracle/sequences.php

Jesse Hautala
  • 134
  • 10
  • Thanks Jesse. I wish I could accept both answers, and give both of you points but this one helped me more because my sequence was already there and I just needed to update it. – Universitas Jul 30 '12 at 20:09
1

You can always set up the start number of the sequence when creating it:

CREATE SEQUENCE my_sequence
    MINVALUE 1000
    START WITH 1000
    INCREMENT BY 1;
Olaf
  • 6,249
  • 1
  • 19
  • 37