DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: oracle Sequence -- Oracle Bug ????

  1. #1
    Join Date
    Jan 2002
    Posts
    17

    Exclamation

    Hi ,

    I am having a strange problem with Oracle Sequences.

    Database :Oracle
    Version:8.1.6

    These are the steps how i encountered the problem.

    CREATE SEQUENCE TEST_SEQ INCREMENT BY 1 START WITH 1 MAXVALUE 25 MINVALUE 1

    SELECT TEST_SEQ.NEXTVAL FROM DUAL;
    NextVal
    ----------
    1
    ....
    .........
    SELECT TEST_SEQ.NEXTVAL FROM DUAL;
    NextVal
    ----------
    20

    SELECT TEST_SEQ.CURRVAL FROM DUAL;
    CURRVAL
    -----------
    20

    DROP SEQUENCE TEST_SEQ;

    Again I am creating the same Sequence

    CREATE SEQUENCE TEST_SEQ INCREMENT BY 1 START WITH 1 MAXVALUE 25 MINVALUE 1

    SELECT TEST_SEQ.CURRVAL FROM DUAL;
    CURRVAL
    -----------
    20

    Eventhough I dropped the sequence and I created it again the currval for the first time is showing the same old incremented sequence value. If I select the NEXTVAL from there onwards the currval will be initialised to new value.

    Is this a desired behaviour Or it it a Oracle Bug ??

    Your comments are greatly appreciated.

    Thanks In Advance,
    Sekhar Modem.








  2. #2
    Join Date
    Dec 2001
    Posts
    26
    Hi,

    I don't know whether this is bug or not, but it seems to be a problem that the sequence is being cached. This is the default behavior, and the default cache size is 20 that explains why the second 'NEXTVAL' returns 20.

    You may try to issue a 'COMMIT' after you drop the sequence and test again, if fail, you can try to alter/create the sequence with 'NOCACHE' option.

    Eddy Ng

  3. #3
    Join Date
    Mar 2001
    Posts
    314
    Originally posted by s_modem
    Again I am creating the same Sequence

    CREATE SEQUENCE TEST_SEQ INCREMENT BY 1 START WITH 1 MAXVALUE 25 MINVALUE 1

    SELECT TEST_SEQ.CURRVAL FROM DUAL;
    CURRVAL
    -----------
    20

    At this point the value TEST_SEQ.CURRVAL is NOT yet defined. How did you get oracle to return a value to you??????

    -amar

  4. #4
    Join Date
    Jan 2002
    Posts
    17

    Oracle Sequence -- Oracle Bug ???

    Hi,

    The Database is Oracle
    Version : 8.1.6


    I tried using commit after dropping the sequence. But it doesnt work.
    I guess this is a bug ??


    Your comments are appreciated.

    Thanks.
    Sekhar Modem.

  5. #5
    Join Date
    Dec 2001
    Location
    Atlanta
    Posts
    175
    Hi s_modem

    try this: This works for me.

    CREATE SEQUENCE TEST_SEQ NOCACHE
    START WITH 1
    INCREMENT WITH 1
    --MAXVALUE 25
    NOCYCLE;

    note: There is no need for minvalue and even maxvalue. The sequence will start with 1 and increment with 1 until 25 is reached. What do you do after 25?

    C. K.
    Apps DBA

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