Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1738
  • 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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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