Oracle Sequence problem
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Oracle Sequence problem

  1. #1
    Join Date
    Sep 2000
    Posts
    12
    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

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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).

  3. #3
    Join Date
    Mar 2000
    Location
    westboro, ma, USA
    Posts
    14

    Thumbs up

    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).
    Rajesh

  4. #4
    Join Date
    Sep 2000
    Posts
    12
    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

  5. #5
    Join Date
    May 2000
    Location
    Portsmouth, NH, USA
    Posts
    378

    Talking maybe ...


    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

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092

    Re: maybe ...

    [QUOTE][i]Originally posted by jgmagnus [/i]
    [B]
    the user might not actually commit and they might do a rollback or something. [/B][/QUOTE]

    This is definitely true. The updating of the sequence number is not part of the transaction.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  7. #7
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    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

  8. #8
    Join Date
    Sep 2000
    Posts
    12
    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.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width