Printable View
Hello, I am trying to track down a sequence quirk I am having. It appears, all of the sudden all my sequences that are defined as "create sequence blah start with 1 increment by 1" are incrementing by 1 sometimes and by 20 most of the time. None of the sequences in question are in use by another user, I checked. I'm using Oracle 8.1.6 running on SunOS 5.7. If anyone has run into this or has some suggestions, they would be greatly appreciated.
TIA
This could be a problem in dual table.
Select * from dual;
If you get only one row, then dual table is OK, otherwise you need to rerun procedure (I think catproc).
in your create sequence command add ' nocache'. The default cache is 20. The sequence caches this much numbers(20 in this case) into the memory and uses them ( for improved performance).
Thanks for the help. I tried both of the suggestions, the dual table seems to be ok, and I dropped one of the sequences in question, created one with a different name with the nocache option and implemented that in place of the old one. It still seems to be doing the same thing. Is there a routine I could write to somehow log when and where this sequence is being used? Don't know if that is possible, but again, any ideas would be appreciated..
TIA
I have found that same quirk.
I sort of figured that it has to do with ACTIVATING a sequence but then the user might not actually commit and they might do a rollback or something. Also, I have noticed this quirk happens after you use the sequence a number of times or the database has inactivity for a certain amount of time.
What do you think?
- Magnus
This is definitely true. The updating of the sequence number is not part of the transaction.Quote:
Originally posted by jgmagnus
the user might not actually commit and they might do a rollback or something.
Sequence is incremented as soon as it's asked to generate a new number; there is no rollback allowed here.
Try setting the cache_size to 1 and see what happens.
- Rajeev
Well, I think the problem has been solved for now, I was going to try the suggestion of setting cache to 1, but the nocache option seems to be working now. There is not much activity against this application at the moment but it seemed to straighten out. Time will tell. Thanks for the input.