This content has been marked as final. Show 8 replies
Drop Sequence ...
Create Sequence ... START WITH ...
(But, don't forget about existing privileges, if any!)
Select nextval as many times needed until you reach the value you need
maybe changing the INCREMENT BY, first
Edited by: Peter on May 21, 2009 9:13 AM
- Added comment on privs
alter sequence YOUR_SEQ increment by &new_value-&actual_position nocache; select YOUR_SEQ.nextval from dual; alter sequence YOUR_SEQ increment by 1 nocache; declare LastValue integer; begin loop select YOUR_SEQ.currval into LastValue from dual; exit when LastValue >= &new_value - 1; select YOUR_SEQ.nextval into LastValue from dual; end loop; end; / alter sequence YOUR_SEQ increment by 1 cache 20;
Edited by: Christian Balz on 21/05/2009 09:09
Edited by: Christian Balz on 21/05/2009 09:14
Drop and create will drop the grants given .
To reset the sequence backwards use -1 to go backwards.
var x_n number ; alter sequence SEQ_one increment by -1 ; begin select SEQ_one.nextval into :x_n from dual ; while ( :x_n > 1 ) Loop select SEQ_one.nextval into :x_n from dual ; end loop ; exception when others then if SQLCODE=-8004 then null ; else raise ; end if ; end ; / alter sequence SEQ_one increment by 1 ; var last number ; begin select the_last_number into :last from dual ; for i in 1..:last loop select SEQ_one.nextval into :x_n from dual ; end loop ; end ; /
As an example of what Peter meant by "changing the INCREMENT BY":
Say seq_x has just generated the numbers ..., 98, 99 and 100.
Now you want to reset it, so that it will generate 1, 2, 3, ...
will produce 1.
ALTER SEQUENCE seq_x INCREMENT BY -99; SELECT seq_x.NEXTVAL FROM dual;
will produce 2 and 3.
ALTER SEQUENCE seq_x INCREMENT BY 1; SELECT seq_x.NEXTVAL FROM dual; SELECT seq_x.NEXTVAL FROM dual;
Make sure no one else is using the sequence while you do this.