Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1636
  • 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

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