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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
