Current sequence number
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Current sequence number

  1. #1
    Join Date
    Jan 2000
    Posts
    387

    Current sequence number

    Hi

    How do I check what is the current number for a sequence?

    seqno.currval cannot be used before issuing seqno.nextval which will have one number skipped in the sequence. Any tables to check what is the current sequence number? All sequences table does not have it. Thanks!

  2. #2
    Join Date
    Apr 2003
    Posts
    353
    SELECT sequence_name, min_value, max_value, increment_by, cycle_flag,
    order_flag, cache_size, last_number
    FROM all_sequences
    WHERE sequence_owner = <>
    AND sequence_name = <>;

  3. #3
    Join Date
    Jan 2000
    Posts
    387
    This will not retrieve the current sequence number. The last number is different from seqno.nextval.

  4. #4
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    wow, i didnt know that...all the time i use nextval coz there will always be skipped aequences no. anyway.
    By the way, this is my first time to see a "dba" title. I haven't got
    that one before

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Jan 2000
    Posts
    387
    I cannot have a missing number in the sequence which has been skipped because of the command seqno.nextval due to some restrictions.

    I realised that the last_number will increased by 20 eventually when the last number matches the current sequence number.. but that's not the number I am looking for. I want to know the current sequence number. Thanks!

  7. #7
    Join Date
    Jan 2000
    Posts
    387
    Thanks jmodic! This is cOoOL!

  8. #8
    Join Date
    Apr 2003
    Posts
    353
    nocache will give the last number.

    1 SELECT sequence_name, min_value, max_value, increment_by, cycle_flag,
    2 order_flag, cache_size, last_number
    3 FROM USER_sequences
    4 WHERE
    5* sequence_name ='TEST2'
    SQL> /

    SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
    ------------------------------ --------- --------- ------------ - - ---------- -----------
    TEST2 1 1.000E+27 1 N N 0 4

    SQL> select test2.nextval from dual;

    NEXTVAL
    ---------
    4

    If you require the previous number minus with the increment_by.

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