Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

altering sequence to start from 0

591162Aug 27 2007 — edited Aug 27 2007
I have a sequence for auto-incrementing a value. Each day I want that sequence to restart, meaning to start from 0 again. So I did a job in which I tried deleting the sequence and re-creating it. Someone told me that instead of deleting-recreating a sequence I can use alter sequence to make the sequence start from 0 again but as I read, the alter sequence cannot do that. as I read:

ALTER SEQUENCE [schema.]sequence
[INCREMENT BY integer]
[MAXVALUE integer | NOMAXVALUE]
[MINVALUE integer | NOMINVALUE]
[CYCLE | NOCYCLE]
[CACHE integer | NOCACHE]
[ORDER | NOORDER]

there is no option to change start from.
Does anyone knows if that can be actually done?

As I figured it's not possible to alter the sequence to start from 0 again I can do a job in oracle to delete and create the sequence again. The thing it it's not working. If I do a job and say:

execute immediate 'drop sequence name_seq'; and execute it, it works, it deletes the squence but if I further write:

execute immediate 'create sequence name_seq ' ||
'start with 1 ' ||
'increment by 1 ' ||
'nomaxvalue ';

it doesn't do it anymore. I get no errors but the sequence is not created. can anyone give me a hand here? 10x a lot in advance
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 24 2007
Added on Aug 27 2007
3 comments
646 views