I am using EJB3, JBoss AS 4.2.1 and Oracle 10g. The thing is every time i deploy to the AS the sequence is broken. Ex: when i am looking to the sequence from toad, its last value is 41 but the actual id which is set for the new entry is 1050. Do you have any idea or a known bug about this issue. Thanks.
+4
A:
Sequences are not gap free !! Thats not a bug.
See : http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:513022131111
guigui42
2009-08-05 12:39:37
If it is a very slowly incrementing sequence (eg only use three of four values a week), then setting NOCACHE wouldn't impact performance.
Gary
2009-08-05 23:00:41
But it still won't guarantee gap free sequences.
RussellH
2010-05-26 17:17:03
+1
A:
From memory, Oracle caches the sequence numbers for faster access, hence the gap. Its expected behaviour.
You can execute an ALTER SEQUENCE command to reset the counter to the correct value.
ozczecho
2009-08-05 12:41:08
I would argue that 1050 *is* the "correct" value. When using sequences and auto-generated key values, all you care about is that "tell me which ID to use that is unique for the new row, that's all I need to know". In other words, if you don't care where you are, you're never lost.
Lasse V. Karlsen
2009-08-14 11:18:27
A:
Thanks both for your replies. My problem was caused because of the default allocationSize of my entities. Default value of the allocationSize is 50. That is, i believe, jboss app is asking oracle db each 50 entity creation. The cache value of the sequencies in the oracle is 20. It means when i insert 1000 records into the database, sequence increases its nextVal 20.