Re: Missing sequences... how to retrieve the missing no?

Unfortunately, even with NOCACHE, there is no guarantee that there will be no gaps between sequence numbers.

When business rules call for no gaps in the id of a table, create an id tracking table to keep track of the last id inserted into the table, something like this:

CREATE TABLE sequence_numbers(

 table_name                      varCHAR2(30)     NOT NULL,
 last_used_seq_number            NUMBER(10)       NOT NULL,
 max_seq_numbers                 NUMBER(10)       NOT NULL

In article <39185143.4B089986_at_euclidsys.com>,   Randy DeWoolfson <randy_at_euclidsys.com> wrote:
> that is not really the point of a sequence - or a primary key...
 these
> just need to be unique.
>
> You could write code to pack down your number sequence and fill
> in the gaps, and then reset your sequence generator- any number of
> algorithms would work, but why? lots of work for little gain...
>
> randy
>
> jess wrote:
>
> > Dear Netizens
> >
> > I am facing the issue of missing sequence number in one of our
 tables. I
> > had set the sequence number to nocache. Hence, the system is slow
 and
> > will need to retrieve the number from the disk whenever it is
 needed.
> >
> > However, individual sequence numbers can be skipped if they were
> > generated and used in a transaction that was ultimately rolled
 back. How
> > do I make provisions to catch and reuse these sequence numbers?
> >
> > Thanks in advanced.
> >
> > Rgds,
> >
> > Jessica
> > <jesskung_at_brel.com>
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue May 09 2000 - 00:00:00 CDT