URGENT PLEASE HELP!!!

[ashley75]

We have this sequence in our database

CREATE SEQUENCE my_sequence
START WITH 60005963
INCREMENT BY 1
MINVALUE 60005883
MAXVALUE 9999999999
NOCYCLE
CACHE 20
NOORDER
/


The user start generate the check last week and sequence was generated fine from 60005883 to 60005934. So today, when she generate check , we expect to have the check number 60005935 but it disn't generate this number and it generate number 60005943.

1. Why it generated 60005943 instead of 60005935 ???
2. What is the cache mean in the sequences

Please help,it's very urgent

[dba1]

Probably your application tried to get the number from the sequence and cancelled the transaction before commit. In this case, the sequence # incremented anyway.

[ashley75]

nope, it's not the case. I am positively sure

do you have any other recomendations???

What is the CACHE in sequence???

[Shestakov]

if u have :
...
CACHE 20 NOORDER
...
then if db has been shutdowned ABORT then u lost 20 (or 19 i don't sure) values from
this sequence.

If u want to have ordered seq numbers then u have to write:

NOCACHE
ORDER

[rpardip]

If you say CACHE 20, it means Oracle will cache 20 sequential numbers in buffer cache and will make them immediately available to you.

However, if you re-start the database, these 20 numbers would be gone. After the re-start, Oracle would make another set of 20 number available to you in the cache.
Due to which, you would see some difference between the present value and the last value before the database re-start.

The cache option is to reduce I/O.

[ashley75]

rpardip,

So what is the solution if I want to generate the next sequence is 60005934, do I have to drop the sequence and recreate it????

How do I prevent this happen in the future b/c I do cold backup every night which mean that I have to bounce the instance every night???

Thanks


[Edited by ashley75 on 05-29-2002 at 05:38 PM]

[marist89]

Yup, the easiest way is to drop & re-create.

If you want to prevent this in the future, don't use a sequence. You might want to look at the Advanced Queuing (AQ) features of Oracle.

[rpardip]

Ashley75,

Like Shestakov said, use NOCACHE or follow Jeff's advise;
drop it and re-create it appropriately or perhaps, you may want to look into AQ option.

In some cases, programmers would create a table to store
the last value and write a function and call the function in a trigger or call it in the front-end script.

[patnams]

I wonder what has AQ to do with sequences. I think both are very different games altogether.

Thanks
Sridhar

[marist89]

AQ has nothing to do with sequences. The user is trying to emulate queues with a sequence which isn't 100% foolproof (as they found).