Re: Oracle Serial numbers

Chris O'Connell wrote:
>
> Glenn Berry wrote:
> >
> > 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.
> >
> > I have made an assumption that you are looking for serial numbers ie 1 -
> > 20 but you actually got 1,2,3,4,5,21,22,23,24,25.
> >
> > This is usually caused by the values being cached and then you lose the
> > instance for some reaseon, the way to avoid this is to set nocache for
> > the sequence.
> >
> > Glenn.
>

It's been my experience that it is very tough to have the database assign serial numbers in a multi user environment. Often, the real requirement is not for serial numbers but for unique id's and that's much easier. If the small gaps look bad even though there's no real reason for absolute sequentiality then you might increment the sequence by a number like 3 or 7. That can then give the numbers an appearance of randomness preventing the casual observer from asking where the missing numbers are.

If there is an audit requirement for actual numbers with no gaps then often it's easier to assign them physically outside the computer from say rolls of barcodes or coded folders like you see for medical records.

The assignment then an audit procedure integrated into a physical business process.

-ed
--  Ed Kulis          408-341-5561        Hal Computer Systems
--  Sr. Oracle DBA    ekulis_at_hal.com      Campbell, CA 95008
--  I would like it to be known that all opinions that are shown
--  are exclusively my own, on my own, all my own.