altering sequence to start from 0
591162Aug 27 2007 — edited Aug 27 2007I 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