Re: Why sequence jumps?

A copy of this was sent to Wassim <net2000_at_francemel.com> (if that email address didn't require changing) On Sun, 27 Jun 1999 16:45:30 GMT, you wrote:

>Hi,
>I'm using Sequences (as many of you told me to do, for generating a
>primary keys in some tables) Strange phenomenen i've noticed is that in
>some cases counter jumps from 21 to 40 or 11 to 34 ? this is source of
>my sequences :
>

sequences default to a cache size of 20 so if the sequence gets aged out of the shared pool or you stop/restart the database -- you can lose that many.

You can use NOCACHE on the create sequence however -- sequences are *not* gap free. All it takes is a sequence of events such as:

select mvt_seq.nextval from dual;

....

rollback;

and that sequence number will be forever gone.

>create sequence mvt_seq
>start with 0
>increment by 1
>nomaxvalue
>minvalue 0
>nocycle
>order (cos order is important for me!)
>
>Is there any problem with this?
>Thanks for help!
>
>Wassim.
>e-mail: net2000_at_francemel.com
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sun Jun 27 1999 - 12:38:38 CDT