Re: Oracle Serial numbers

huele_at_pacbell.net wrote:
>
> I want to have serial number on my table record by using "nextval", but
> the number go from 1,2,3,4,5 then 21,21,24,25. Any expert can help me
> out?
> Thanks alot.
In your INI file there is a parameter SEQUENCE_CASH_ENTRIES this is number of values will be cashed in memory. In case you shut down database (for example), some values will be lost. When you create seq. use NOCACHE option. It will greately reduse, but not eliminate possibility of "holes".
For example:
.CURRVAL is 10.
You start transaction and use .NEXTVAL. Immediately after you do it sequience "commits" increasing of the seq. value. Now your .CURRVAL is 11. After you roll your transaction back, and issue another time, .NEXTVAL will be 12, not 11 as you could wish. 11 will be a "hole" inside your table.
The only way you can get sequencial value is to create your own "SEQ" table and use it as a part of your transaction. You can "select for update" from it to lock nessesary record. It will be commited and rolled back in the normal transactional schema. Note: in this case could be another multiuser problem: If multiple users use this record very often, it will cause frequently rolling back (multiversion control). Try to commit just immediately after increasing value.

Hope it helps,

Eugene.

-- 
********************************************************
*        Everything above is only my opinion           *
********************************************************
*						       *
* If you see a lion in a cage and sign says "elephant" * 
*                      ,                               *
*           DO not belive your eyes !                  *
*       (Kozma Prutkov. (informal translation))        *
*                                                      *
********************************************************