Solved

how to update user_sequences

Posted on 2009-05-19
10
1,468 Views
Last Modified: 2013-12-18
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
Comment
Question by:jmarkfoley
  • 5
  • 3
  • 2
10 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24419748
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
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 24419788
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24419816
>Note that you can't reduce the value of the sequence!
I have to disagree with that :)
0
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 24419842
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
 
LVL 1

Author Comment

by:jmarkfoley
ID: 24419950
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24419978
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
 
LVL 1

Author Comment

by:jmarkfoley
ID: 24420139
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
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 24420974
you have to set it to the - current_value ...
0
 
LVL 1

Author Comment

by:jmarkfoley
ID: 24441906
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24462638
>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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now