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

Thread: to find last number generated for sequence object

Hybrid View

  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
    Jul 2006
    Posts
    96

    how to view v$_sequence ( not available)

    hi v$_sequences, is not available , i checked.
    how to create that is there any built in script?





    Quote Originally Posted by JChiappa
    One can use v$_sequences , as said by HJR in http://www.dizwell.com/prod/node/59

    Regards,

    Chiappa

  10. #10
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,003
    Quote Originally Posted by madhugp
    hi v$_sequences, is not available , i checked.
    how to create that is there any built in script?
    You need to do an explicit grant select to the schema owner for it to be available.
    this space intentionally left blank

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