Oracle-L: RE: strange behaviour of sequence

From: BanarasiBabu Tippa <btippa_at_ORsoftware-india.com>
Date: Wed, 29 Jan 2003 03:44:27 -0800
Message-ID: <F001.0053D0B9.20030129034427@fatcity.com>

Hi Jp

The gaps in sequences can most likely be attributed to the sequence being cached. The cache option (default) pre-allocates a set of sequence numbes and keeps them in memory so that sequence numbers can be accessed faster. When the last of the sequence numbers in the cache has been used, Oracle reads another set of numbers into the cache. When the instance is shutdown, sequence numbers that have been cached but not used are lost. Also sequence numbers that have been used but not saved are lost as well.

It is also possible for cached sequences to be aged out of the library cache if the shared pool is flushed or when the shared pool is under heavy load and needs to free space frequently. Sequences are candidates to be removed from the shared pool in order to make space. Using dbms_shared_pool.keep() to pin the sequences addresses this issue.

Individual sequence numbers can be skipped if they were generated and used in a transaction that was ultimately rolled back. Applications would have to make provisions to catch and reuse these sequence numbers, if desired.

Basically, the only way to guarantee that sequences are not skipped is to specify the NOCACHE option with the CREATE or ALTER SEQUENCE command

thanks
Banarasi

-----Original Message-----
Sent: Wednesday, January 29, 2003 4:04 PM To: Multiple recipients of list ORACLE-L

Guys,

one of my developers is using sequence to auto-increment the value of a column while inserting.

he has created a sequence like this.

SQL > create sequence testseq start with 1;

and then uses a INSERT statement as below in a JSP.

insert into testtab values ('BREAD'||testseq.nextval);

after some inserts .....when he does SELECT from TESTTAB...he finds
the values as :

BREAD1
BREAD2
BREAD3
BREAD4
BREAD21
BREAD22 it should increment by 1.but it is not so ?

any hint/clue ????

Regards,
Jp.

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: oraora oraora
INET: oraoraora_at_rediffmail.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: BanarasiBabu Tippa
INET: btippa_at_ORsoftware-india.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------