We help IT Professionals succeed at work.

Jumpy Oracle 8.1.7 sequences

aqualung
aqualung asked
on
I have a sequence that is not creating consistant sequence numbers. Parameters are:

1) Increment by: 2
2) Cache: 20
3) Min Value: 1
4) Max Value: 999999999
5) Recycle and Guarentee Order are not checked
6) Oracle 8.1.7

Sequences will generate normally for a short period of time (minutes) and then jump by 40 (last_number in dba_sequences increases by 40 after the last successful increment by 2). If I decrease the cache to 10, then dba_sequences.last_number will jump by 20. All I want is for the sequence to consistantly increment by 2. What am I doing wrong?
Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
I have not heard that the secquence jump, but that the values used from the sequence might be dropped if the statements that request the values are rolled back...

I don't know how you check this, but i would test it with a small script/program on a test sequence...

CHeers
Indeed, when the transaction is rolled back the sequence remains at the same value and doesn't get "rolled back" to the value it had at the begining of the transaction.
Plus, if it has a cache (not with nocache), the cached values are lost when the database instance is shut down or happens to fail for some reason.

Author

Commented:
Many thanks to bogdincescu! I agree with the answer in terms of rollbacks and crashes, but while waiting for a response, I found that a better solution was to prevent sequences from ageing out of the library cache by pinning them using dbms_shared_pool.keep().  Pinning the sequence will prevent the sequence values from being aged out of the cache.  Pinning the sequence is achieved by invoking the rdbms package dbms_shared_pool.keep() as follows:

dbms_shared_pool.keep('SEQUENCE_NAME','Q').

Author

Commented:
Many thanks to bogdincescu! I agree with the answer in terms of rollbacks and crashes, but while waiting for a response, I found that a better solution was to prevent sequences from ageing out of the library cache by pinning them using dbms_shared_pool.keep().  Pinning the sequence will prevent the sequence values from being aged out of the cache.

Author

Commented:
Many thanks to bogdincescu! I agree with the answer in terms of rollbacks and crashes, but while waiting for a response, I found that a better solution was to prevent sequences from ageing out of the library cache by pinning them using dbms_shared_pool.keep().  Pinning the sequence will prevent the sequence values from being aged out of the cache.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.