to find last number generated for sequence object
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 24

Thread: to find last number generated for sequence object

  1. #1
    Join Date
    Jul 2006
    Posts
    96

    to find last number generated for sequence object

    hi , how to find last number generated for a sequence object,
    without generating 'nextval'

    is it last_number column of user_sequences?

    but it is showing wrong value!!

  2. #2
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    Edited
    Last edited by simply_dba; 01-02-2007 at 12:35 AM.
    There are three kinds of lies: Lies, damned lies, and benchmarks...

    Unix is user friendly. It's just very particular about who it's friends are.

    Oracle DBA

  3. #3
    Join Date
    Dec 2006
    Location
    Brazil
    Posts
    36
    You have to check if your sequence is cached. The "CACHE_SIZE" field on user|all|dba|sequences show if it is and the "CACHE_SIZE" field show the cache size. If your database just hung you will lost any cache sequence number.

    Regards

    Carlos Duarte
    Oracle Applications DBA

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Use SEQUENCE_NAME.CURRVAL.
    SQL> select tamil_seq.nextval from dual;

    NEXTVAL
    ----------
    2

    SQL> select tamil_seq.currval from dual;

    CURRVAL
    ----------
    2

    CURRVAL will work only after you executed nextval, oherwise you will get an error:

    SQL> select tamil_seq.currval from dual;
    select tamil_seq.currval from dual
    *
    ERROR at line 1:
    ORA-08002: sequence TAMIL_SEQ.CURRVAL is not yet defined in this session


    Tamil
    Last edited by tamilselvan; 01-02-2007 at 10:45 AM.

  5. #5
    Join Date
    Sep 2000
    Location
    Sao Paulo,SP,Brazil, Earth, Milky Way
    Posts
    350
    One can use v$_sequences , as said by HJR in http://www.dizwell.com/prod/node/59

    Regards,

    Chiappa

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Quote Originally Posted by JChiappa
    One can use v$_sequences , as said by HJR in http://www.dizwell.com/prod/node/59

    Regards,

    Chiappa
    Which column in v$_sequences will tell you the last value the sequence generated?

    Tamil

  7. #7
    Join Date
    Dec 2006
    Location
    Brazil
    Posts
    36
    Try to check HIGHWATER.
    Regards,

    Carlos Duarte
    Oracle Applications DBA

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Quote Originally Posted by chduarte
    Try to check HIGHWATER.
    HIGHWATER with cache option will not tell you the last number generated.

    Tamil

  9. #9
    Join Date
    Dec 2006
    Location
    Brazil
    Posts
    36
    That's what I said. I think not possible to get current value if your sequence is cached.
    Regards,

    Carlos Duarte
    Oracle Applications DBA

  10. #10
    Join Date
    Dec 2006
    Location
    Brazil
    Posts
    36
    For performance efficiency, Oracle uses caching with a default cache value
    of 20. That means that when you enter a transaction, 20 numbers are read
    into memory. The next time someone wants a number from the sequence, Oracle
    reads memory for the next number. It doesn't have to perform a disk I/O to
    read the next number from the sequence.

    That means that when memory is cleared after you commit a transaction and
    exit the form, numbers left in the cache are lost. That's why you get
    gaps in the number sequence between 1 and 20.

    You can avoid the problem by changing the sequences as follows:

    ALTER SEQUENCE XXX NOCACHE;
    Regards,

    Carlos Duarte
    Oracle Applications 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