Re: Order of sequence number generation

From: John Y. Chan <john_at_rte-asset.com>
Date: 1997/10/31
Message-ID: <345A50D4.1420@rte-asset.com>#1/1

Joseph D. Sumalbag wrote:
>
> Christian Derreth wrote:
> >
> > Sabarinath Nair wrote:
> > >
> > > Hello,
> > >
> > > I have a sequence created using the foolowing SQL.
> > >
> > > create sequence SEQ_MESSAGE_ID INCREMENT BY 1 START WITH 100 maxvalue
> > > 999999999999 ORDER;
> > >
> > > When I use this sequence, thru a stored procedure, the sequence numbers
> > > which are generated are not incrimented by 1. instead it gets increments
> > > of 20. 1, 21, 42 etc...
> > >
> > > Does any one know how to get around this...
> > >
> > > Thanks
> > > Sabari
> >
> > Dear Sabarinath,
> >
> > this may be due to sequence number caching. Try the NOCACHE parameter.
> >
> > Regards, Christian.
>
> Or it could also be that the user obtained the number first
> and committed it last .....That way the later number will apper
> to have been first before the least number .
>
> I usually dont like using this sequence number when I'm
> playing with controlled number such as check numbers and
> others.... coz when a user obtained them and did not commit
> that number is lost or if you shutdown the Db, every sequence
> number in your cache is gone , but they are really good when you
> need to supply unique keys .
>
> Ciao !
> ================================================================
> ============================
> | Joseph Sumalbag
> |
> | Oracle DBA
> |
> |
> |
> | The opinions expressed above are my own and doesn't

 necessarily                           |

> |reflect the opinion of any of my client company or my employer.
> |
> ================================================================
> ============================

I had the same problem and found out there were 20 rows in system.dual table
by some reason. if the SQL statement goes like this

	select sequence_no.nextval
	from dual;

I generated 20 numbers. I dropped system.dual, make sure synonym of dual is sys.dual
and only one row in sys.dual. the problem disapear. Hope this helps.

-- 
John Y. Chan
Manager, Data Services
RTE Asset Management (aka. Rightime Econometrics, Inc.)
1095 Rydal Rd.
Rydal, PA 19046
(215) 572-7288 (Voice)
(215) 572-6254 (Fax)

Received on Fri Oct 31 1997 - 00:00:00 CST