Sequence.nextval incrementing itself over time

[arachno]

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

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?

Example:

INSERT INTO hprequests (requestid, username, flagtype, reqstatus, workstatus, requesttitle, statuschangedby)
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?

[r035198x]

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?

[arachno]

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...

[r035198x]

What's your sequence definition like? Do you have a trigger on the table for the sequence?

[arachno]

My sequence was defined like this:

create sequence SEQREQUESTID
increment by 1
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?

[arachno]

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.

[debasisdas]

in the sequence defination use the NO CACHE key word.

because if you restart the server all the values in the cache are lost.

[Dave44]

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.

[arachno]

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:

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!