Re: Sequence Number Re-use is it possible, what is the standard

You should ask yourself why are you worried about losing a sequence number. The only valid reason I can think of is for auditing purposes, i.e. you need to prove unbroken sequences. If this is the case you'll have to implement a numbering table which is updated in the same transaction as the insert, and will rollback in case of failure. The insert can be in a trigger (before insert, on each row) for simplicity.

Sequences are meant to provide unique ids, not for audit logging.

"Anjo Kolk" <k.kolk_at_chello.nl> wrote in message news:3A9E4470.1C8F8114_at_chello.nl...
>
> If you have 200 concurrent users of this sequence, you want to consider
 not
> using no cache. Else you will see wait events on enqueue for trying to
> allocate a new sequence number (SQ enqueue).
>
> Anjo.
>
>
> Michel Cadot wrote:
>
> > "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> a écrit dans le message
 news:
> > 57fq9tkfvmsqnsja55sd70o6blbq2dt33d_at_4ax.com...
> > > On Wed, 28 Feb 2001 16:59:37 GMT,
> > > u28656005_at_spawnkill.ip-mobilphone.net wrote:
> > >
> > > >We have a system that uses a Sequence table to get id's for our bids.
> > > >
> > > >In our application the user might click new, which gets the nextval.
> > > >
> > > >But if they click new again, or they close the program, we obviously
> > > >
> > > >lose that number from the sequence table. What do most people do
 about
> > > >
> > > >this. We really don't want to lose this number, and there will
> > > >
> > > >eventually be over 200 users getting unique id's from our sequence.
 So
> > > >
> > > >unused numbers can go pretty rapidly.
> > > >
> > > >Has anyone done something like, put the unused numbers into a table,
> > > >
> > > >have the program first look to see if there are any record in this
> > > >
> > > >table, and grab one of these numbers and uses it, and if they find no
> > > >
> > > >records it goes to the sequence table. Is this a stupid idea?
> > > >
> > > >
> > > >
> > > >Inquiring minds want to know
> > > >
> > > >Thank you very much for your help.
> > > >
> > > >
> > > >
> > > >Mark
> > > >
> > > Use the nocache option of the create sequence command, which will at
> > > least take away the buffer of pre-created sequence numbers, and for
> > > the rest : don't bother. The maximum sequence number is 2^32 -1, which
> > > you probably won't even reach before you retire.
> > >
> > > Hth,
> > >
> > >
> > > Sybrand Bakker, Oracle DBA
> >
> > I agree with Sybrand except for the maximum sequence number which
> > is 999999999999999999999999999 in Oracle7 and 10**27 with Oracle8i.
> >
> > --
> > Cheers
> > Michel
>
Received on Thu Mar 01 2001 - 07:27:32 CST