Sequences increment in 20's!! 1

[MattWoberts]

Sequence numbers have been incrementing in groups of 20 at a time. I am assuming this is because we are using a setting of 'CACHE 20'. Although this is itself shouldn't causes these increments, it may be because the application accessing the database is a web application, so I presume it is constanly making and then breaking connection with the oracle server, therefore losing the remainder of the cached sequence numbers. Can this situation be confirmed/denied? Should NOCACHE be used on a sequence number when the database is being used for a web application?

Another problem has come to light, to which we have no clues. At some stage the next sequence number has been changed so it points to a lower number. We don't know how. Now, when records are added, and we try and get the ID of this record by using a simple 'select max(ID)...' statement, it retreives the wrong record. What's more, there are constraint errors when it tries to overwrite an existing record. Any suggestions on what may have caused the sequence number to change would be useful.

 

[Wonboodoo]

You'll only lose sequence numbers in Oracle if you shut it down and restart the Oracle instance. That's the only time the sequence cache will be lost.

What is your webserver? If EJB then maybe it's doing something funky and grabbing whole bunches of sequences at once.

As for the sequence number dropping to a lower number. It can happen if it's a sequence that cycles. You can check by looking at the DBA_SEQUENCES data dictionary table. Other than that the only way is if someone (or some application) changed the sequence (maybe dropping and recreating it).

 

[carp]

Concur on the caching.

Another way the sequence could be coming up with a lower number is if somebody has set up another routine that generates a number WITHOUT consulting the sequence (e.g., it does a "SELECT max(id) + 1" to derive a value). In that case, your key value would be getting populated and the sequence would stand still. When an application hits the sequence for a new number, it will be lower.

 

[Wonboodoo]

There is another reason that the sequence cache can be lost (besides database shutdown). The cache is kept in the shared pool so can be expired if it hasn't been used for a long period. You can either PIN it in the shared pool or turn it into a nocache sequence.

 

[MattWoberts]

Thanks for the info!

We are coming to the conclusion that someone has manually recreated the sequences - or incorrectly exported them and then failed to import them correctly at a later date.

It seems that the sequences do increment irratically sometimes - but thats not a problem for us - I was jyst wondering if it was the cache 20 setting, and it doesn't seem to be.