to find last number generated for sequence object

[slimdave]

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.

[chduarte]

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

[hacketta1]

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.

[chduarte]

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

[yxez]

u r clever dear

[JChiappa]

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

[chduarte]

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.

[JChiappa]

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

[chduarte]

Absolutely agree.

[madhugp]

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