URGENT PLEASE HELP!!!
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: URGENT PLEASE HELP!!!

  1. #1
    Join Date
    Jan 2002
    Posts
    474
    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

  2. #2
    Join Date
    Jan 2002
    Location
    Canada
    Posts
    195
    Probably your application tried to get the number from the sequence and cancelled the transaction before commit. In this case, the sequence # incremented anyway.

  3. #3
    Join Date
    Jan 2002
    Posts
    474
    nope, it's not the case. I am positively sure

    do you have any other recomendations???

    What is the CACHE in sequence???


  4. #4
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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

  5. #5
    Join Date
    Sep 2000
    Posts
    155
    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.

  6. #6
    Join Date
    Jan 2002
    Posts
    474
    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]

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    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.
    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."

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


  9. #9
    Join Date
    Apr 2002
    Location
    Phoenix, AZ
    Posts
    175
    I wonder what has AQ to do with sequences. I think both are very different games altogether.

    Thanks
    Sridhar

  10. #10
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    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).
    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."

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