to find last number generated for sequence object

[madhugp]

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!!

[simply_dba]

Edited

[chduarte]

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

[tamilselvan]

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

[JChiappa]

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

Regards,

Chiappa

[tamilselvan]

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

[chduarte]

Try to check HIGHWATER.

[tamilselvan]

Try to check HIGHWATER.

HIGHWATER with cache option will not tell you the last number generated.

Tamil

[madhugp]

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

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

Regards,

Chiappa

[gandolf989]

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.