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

Edited

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

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

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

Regards,

Chiappa

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

Try to check HIGHWATER.

Quote:

---

Originally Posted by chduarte

Try to check HIGHWATER.

---

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

Tamil

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

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;

Quote:

---

Originally Posted by chduarte

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

ALTER SEQUENCE XXX NOCACHE;

---

I'd say "disable the feature" rathr than "avoid the problem", since in doing so you are potentially ruining your system performance.

Totally agree. Actually "avoid the problem" means avoid the problem with sequence gaps.

Quote:

---

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

ALTER SEQUENCE XXX NOCACHE;

Actually "avoid the problem" means avoid the problem with sequence gaps.

---

But you cannot avoid the problem; there is no such thing as a gapless sequence. For example, even with nocache set if someone calls seq.nextval and doesn't insert the value somewhere (or does insert it and later rolls back their transaction) that number is gone.

Sure. So let's say, try to minimize gap. :)

u r clever dear :D

Tamilselvan, Carlos : sorry for the delay, but the answer "HIGHWATER" given by Carlos is plain wrong : as said by HJR in the page that I cited (at the bottom of the page, read carefully) the correct answer is NEXTVALUE. My tests in a 9ir2 EE database :

sid=BDDEV:BDDEV:/u1/app/oracle>sqlplus /nolog

SQL*Plus: Release 9.2.0.5.0 - Production on Qua Jan 3 14:55:46 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> conn sys as sysdba
Enter password:
Connected.

==> user SCOTT have a sequence...

SQL> select nextvalue, min_value, max_value, cache_size, highwater from v$_sequences
2 where sequence_name='SEQ_DEPT' and sequence_owner='SCOTT';

NEXTVALUE MIN_VALUE MAX_VALUE CACHE_SIZE HIGHWATER
---------- ---------- ---------- ---------- ----------
23 1 1,0000E+27 20 41

==> lets try to use the sequence (see, the sequence IS cached)...

scott@BDDEV:SQL>l
1 select MIN_VALUE, MAX_VALUE, CACHE_SIZE, LAST_NUMBER from user_sequences
2* where sequence_name='SEQ_DEPT'
scott@BDDEV:SQL>/

MIN_VALUE MAX_VALUE CACHE_SIZE LAST_NUMBER
------------------ ------------------ ------------------ ------------------
1 1,000000000000E+27 20 41

scott@BDDEV:SQL>select SEQ_DEPT.nextval from dual;

NEXTVAL
------------------
23
==> yeah, 23 was the sequence next number, NEXTVAL in the sys object worked... let´s advance it :

scott@BDDEV:SQL>/

NEXTVAL
------------------
24

scott@BDDEV:SQL>/

NEXTVAL
------------------
25

scott@BDDEV:SQL>/

NEXTVAL
------------------
26

scott@BDDEV:SQL>/

NEXTVAL
------------------
27

scott@BDDEV:SQL>/

NEXTVAL
------------------
28

scott@BDDEV:SQL>/

NEXTVAL
------------------
29

scott@BDDEV:SQL>/

NEXTVAL
------------------
30

scott@BDDEV:SQL>/

NEXTVAL
------------------
31

scott@BDDEV:SQL>/

NEXTVAL
------------------
32

scott@BDDEV:SQL>/

NEXTVAL
------------------
33

scott@BDDEV:SQL>/

NEXTVAL
------------------
34

scott@BDDEV:SQL>/

NEXTVAL
------------------
35

scott@BDDEV:SQL>/

NEXTVAL
------------------
36

scott@BDDEV:SQL>/

NEXTVAL
------------------
37

scott@BDDEV:SQL>/

NEXTVAL
------------------
38

scott@BDDEV:SQL>

==> lets see the SYS object again :

SQL> l
1 select nextvalue, min_value, max_value, cache_size, highwater from v$_sequences
2* where sequence_name='SEQ_DEPT' and sequence_owner='SCOTT'
SQL> /

NEXTVALUE MIN_VALUE MAX_VALUE CACHE_SIZE HIGHWATER
---------- ---------- ---------- ---------- ----------
39 1 1,0000E+27 20 41

==> again correct, 38 was just used, 39 will be the next.... But FOR SURE, don´t trust in sequences for gap free, never...

[]s

Chiappa

My test:

create sequence seq_test start with 1 increment by 1 cache 20;
select seq_test.nextval from dual;
1

SEQUENCE_NAME NEXTVALUE MIN_VALUE CACHE_SIZE HIGHWATER
--------------- ---------- ---------- ---------- ---------
SEQ_TEST 2 1 20 21

select seq_test.nextval from dual;
2

SEQUENCE_NAME NEXTVALUE MIN_VALUE CACHE_SIZE HIGHWATER
--------------- ---------- ---------- ---------- ---------
SEQ_TEST 3 1 20 21

select seq_test.nextval from dual;
3

SEQUENCE_NAME NEXTVALUE MIN_VALUE CACHE_SIZE HIGHWATER
--------------- ---------- ---------- ---------- ---------
SEQ_TEST 4 1 20 21

SQL> shutdown abort;
SQL> startup;

select seq_test.nextval from dual
21

SEQUENCE_NAME NEXTVALUE MIN_VALUE CACHE_SIZE HIGHWATER
--------------- ---------- ---------- ---------- ---------
SEQ_TEST 22 1 20 41


All numbers in cache have missed.

Yes, after a SHUTDOWN ABORT (or similar db stops) all in the caches is lost, this is one of the "issues" with it - and at a level, with v$ and the like, consistency do not plays a role here... If it fits the bill, use it, but using the value in NEXTVALUE not in HIGHWATER....

regards,

Chiappa

Absolutely agree.

hi , why do the sequence numbers are missing if i use
cache option?
there is no shutdown abort, nor clearing the shared pool etc...
is there any possibility at users side i.e application side.??
if u suggest use NOCACHE option, then how to utilize cache option?

by madhu

you are not the only one flushing shared pool, the kernel does that internally too!

The only time that I really care about the last sequence is when I do an insert. And if I insert a new value with a sequence, and I care what the number is, then I use the returning clause. All this talk about gapless sequences reminds me of the posts about number of extents per table/index.

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

---

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. :rolleyes: