Mark
asked on
how to update user_sequences
I'm running this query:
update user_sequences set last_number = 1 where sequence_name = 'CASE_SEQ'
I get "insufficient privileges" even though I a logged in as the database owner. Nor can I "grant update on user_sequences ..." What's up with this? I'd like to be able to reset my sequence without deleting and recreating it.
update user_sequences set last_number = 1 where sequence_name = 'CASE_SEQ'
I get "insufficient privileges" even though I a logged in as the database owner. Nor can I "grant update on user_sequences ..." What's up with this? I'd like to be able to reset my sequence without deleting and recreating it.
You can't update through the dictionary views, you must use the alter sequence statement to change the sequence values e.g.
alter sequence myseq increment by 20;
Note that you can't reduce the value of the sequence!
If you want to reset the sequence, you will need to drop it and then create it again, specificying the required value for START WITH e.g.
create sequence myseq start with 1 increment by 1;
alter sequence myseq increment by 20;
Note that you can't reduce the value of the sequence!
If you want to reset the sequence, you will need to drop it and then create it again, specificying the required value for START WITH e.g.
create sequence myseq start with 1 increment by 1;
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
>Note that you can't reduce the value of the sequence!
I have to disagree with that :)
I have to disagree with that :)
Sorry, angelIII is correct, you can supply a negative value to the "increment by" clause. It's been a while since I did this ;-).
ASKER
The "increment by" solution doesn't work; at least the way I've gone about it. In the snippet below, "before" is the setting of my sequence before doing the alter. Then I ran:
alter sequence case_seq increment by -14
"after" is the result. You can see that it both set the increment_by value to -14 and the last_number to 0. When I tried to do 'select case_seq.nextval from dual' I got "sequence CASE_SEQ.NEXTVAL goes below MINVALUE and cannot be
instantiated." So, it now wants to generate the sequences -14, -28, ....
alter sequence case_seq increment by -14
"after" is the result. You can see that it both set the increment_by value to -14 and the last_number to 0. When I tried to do 'select case_seq.nextval from dual' I got "sequence CASE_SEQ.NEXTVAL goes below MINVALUE and cannot be
instantiated." So, it now wants to generate the sequences -14, -28, ....
SEQUENCE_NAME MIN_VALUE INCREMENT_BY LAST_NUMBER
---------------- ---------- ------------ -----------
before CASE_SEQ 1 1 15
after CASE_SEQ 1 -14 0
please reread the link: after you changed the sequence and fetched 1 value, you have to alter the sequence "back to normal":
alter sequence case_seq increment by -14;
select case_seq.nextval from dual;
alter sequence case_seq increment by 1;ASKER
Well, I already went ahead and dropped/created the sequence, but when I did #1 and #2 from your example I got the "below MIN_VALUE" error. Maybe I should have set it to -13.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yeah, I guess that would make sense mathematically. Maybe I'll try that next time. The solution isn't intuitive. It seems like it's easier to just drop the sequence and recreate it with a new min_value.
>it seems like it's easier to just drop the sequence and recreate it with a new min_value.
yes, but with the drawback that any trigger/procedure/etc that use that sequence explicitly will be marked as invalid immediately, and the first usage of them will fail with an error message, but request recompilation (which, in case successful), will clear the "invalid status".
yes, but with the drawback that any trigger/procedure/etc that use that sequence explicitly will be marked as invalid immediately, and the first usage of them will fail with an error message, but request recompilation (which, in case successful), will clear the "invalid status".
http://www.techonthenet.com/oracle/sequences.php
the last FAQ is what you need to do.