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

01-02-2007, 01:33 AM

simply_dba

Edited

01-02-2007, 09:24 AM

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

01-02-2007, 11:38 AM

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

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

Tamil

01-02-2007, 02:32 PM

chduarte

Try to check HIGHWATER.

01-02-2007, 03:43 PM

tamilselvan

Quote:

Originally Posted by chduarte

Try to check HIGHWATER.

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

Tamil

01-02-2007, 04:10 PM

chduarte

That's what I said. I think not possible to get current value if your sequence is cached.

01-02-2007, 04:58 PM

chduarte

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.