[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1687
  • Last Modified:

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.
0
jmarkfoley
Asked:
jmarkfoley
  • 5
  • 3
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you cannot UPDATE the sequence just like that, not even as owner.
http://www.techonthenet.com/oracle/sequences.php
the last FAQ is what you need to do.
0
 
MilleniumaireCommented:
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;

 
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Note that you can't reduce the value of the sequence!
I have to disagree with that :)
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
MilleniumaireCommented:
Sorry, angelIII is correct, you can supply a negative value to the "increment by" clause.  It's been a while since I did this ;-).
0
 
jmarkfoleyAuthor Commented:
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, ....



       SEQUENCE_NAME     MIN_VALUE INCREMENT_BY LAST_NUMBER
       ---------------- ---------- ------------ -----------
before CASE_SEQ                  1            1          15
after  CASE_SEQ                  1          -14           0

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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;

Open in new window

0
 
jmarkfoleyAuthor Commented:
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.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you have to set it to the - current_value ...
0
 
jmarkfoleyAuthor Commented:
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.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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".
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now