Re: sequence count not correct

Hi,

We had the same problem with some of our sequences. When you create a sequence, one of the the default settings is "cache", which means that the sequence will pre-alloate and keep in memory a certain number of values (default is 20). If you use more than this in an insert sitting, then you have no problems. If you use less than this in a sitting (as you appear to be doing) then it junks the unused values, and starts at 21.

You can fix this by setting the option to NOCACHE,

alter sequence your_schema.sequence_name nocache;

or if you want to change the number of cached values then

alter sequence your_schema.sequence_name cache 5;

Cheers

Sue Edgecombe

fumi wrote in message <7o691t$ro3$2_at_news.seed.net.tw>...
>
>Steffi <steffi_at_planet.de> wrote in message
news:378DD03E.5BB5F71A_at_planet.de...
>> hi everybody.
>>
>> In my tables I use as ID a sequence.
>>
>> If I insert (nr.nextval) it's ok...the sequence is 1 to 5. Yes it's ok
>>
>> But if I stop and start my database already then my sequence
>> is 21 and not 6. hm..I don't understand this. Can anybody help me??
>
>
>The reason is that you did NOT shutdown database NORMALLY.
>You definitely restart the OS, not the database.
>It's not a good practice at all.
>It causes Oracle to SHOTDOWN ABORT, so the cached sequence value will be
lost.
>
>The way to avoid is to shutdown Oracle normally before you shutdown OS.
>The passive way is to create sequence using NO CACHE option.
>
>
Received on Wed Aug 04 1999 - 02:13:43 CDT