469,271 Members | 1,014 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,271 developers. It's quick & easy.

Sequence.nextval incrementing itself over time

My Oracle sequences seem to be auto-incrementing themselves "over time".
My row ID's are sequenced like this:

Expand|Select|Wrap|Line Numbers
  1. 1, 4, 5, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 41, etc
I'm using "sequencename.nextval" when inserting new rows into my tables, so one would think they'd come in a straight sequence?


Expand|Select|Wrap|Line Numbers
  1. INSERT INTO hprequests (requestid, username, flagtype, reqstatus, workstatus, requesttitle, statuschangedby)
  2. VALUES(seqrequestid.nextval, 'username', 'flagtype', 'reqstatus', 'workstatus', 'reqtitle', 'statuschangedby')
They seem to be increasing properly when I insert several rows in a row (no pun intended), but if I wait say, a day or two, before inserting another row - it may have been increased by 10 or what not.

I'm using Oracle 9 and OleDB objects in C# to execute the queries.

Have anyone experienced anything like this?
Could it somehow be related to a query failing? Would "execution" of a query increase the value of the sequence despite the failure of the query?
Jul 31 '08 #1
8 10681
13,262 8TB
If your id is set to auto increment then why are you providing it in your insert statements? You are trying to increment the id's yourself while the database is also trying to do the same thing for you?
Jul 31 '08 #2
I probably shouldn't have used the word "auto-increment". As far as I know a field can't be set to auto-increment in Oracle (as it can with e.g. MySQL). That's why I need the sequence to increment for me.

I'll try to rephrase myself:
What I ment was that the sequence seems to be incrementing by itself. Even if my application isn't used. So the next time the application is used, and a row is inserted, the sequence does not provide the correct "nextval" but rather a too high value.

I tried to run bad queries from the application on purpose, making them fail, but that didn't provoke the sequence increase either. It seems to be related to time somehow...
Jul 31 '08 #3
13,262 8TB
What's your sequence definition like? Do you have a trigger on the table for the sequence?
Jul 31 '08 #4
My sequence was defined like this:

Expand|Select|Wrap|Line Numbers
  1. create sequence SEQREQUESTID
  2. increment by 1
  3. start with 1;

I don't have any trigger on the table for the sequence (as far as I'm aware of...?)
I'm not sure I quite followed you on that one?
Did you mean if there is any reference between the sequence and the table, or the ID field? The answer to that would be no. Should there be? Is there some way to tell the ID field to use that particular sequence rather than using seqrequestid.nextval?
Jul 31 '08 #5
I just noticed something rather strange:

It seems to be skipping to "n1" (not without exceptions though...)
So, it has skipped to 21, 41, 61 and 81 so far.
Jul 31 '08 #6
8,127 Expert 4TB
in the sequence defination use the NO CACHE key word.

because if you restart the server all the values in the cache are lost.
Jul 31 '08 #7
153 100+
also any failed attempts to insert will lose the grabbed sequence value. all the sequence truly guarentees is a unique value. several conditions can cause "skips" in the numbers.
Aug 1 '08 #8
Debasisdas is on to something. I looked up the no cache thing and found the following which should explain the issue:

When used in a non-cache mode, an access of a sequence which requests
the 'nextval' will increase the current value by the number specified
in the 'increment' section of the sequence and return the new value.
As an example, for a sequence with a current value of zero, an
increment of one, and no cache, the current value would become one,
and one would be returned to the calling statement.

When used with a cache, an access of the sequence which requests the
'nextval' will increase the current value by the increment times the
cache, and will return to the calling function the current value plus
one times the increment. The following access of the nextval *should*
access the cached seqence values and return a number which equals the
previous plus the increment. Ideally, numbers will continue to be
returned from the cache until it has reached the end.

Taken from this article.

As I also needed to display my ID's, I found another way that also seems to have solved the skipping issue. I select the nextval before inserting:

Expand|Select|Wrap|Line Numbers
  1. SELECT seqrequestid.nextval FROM DUAL
I have done some testing on this and I haven't had any skips since I switched to this method.
Thanks for your input everyone!
Aug 4 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by David Stockwell | last post: by
2 posts views Thread by dan | last post: by
2 posts views Thread by Oscar Tuscon | last post: by
7 posts views Thread by urban.widmark | last post: by
7 posts views Thread by Rahul B | last post: by
7 posts views Thread by Haneef | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.