oracle Sequence -- Oracle Bug ????

[s_modem]

Hi ,

I am having a strange problem with Oracle Sequences.

Database :Oracle
Version:8.1.6

These are the steps how i encountered the problem.

CREATE SEQUENCE TEST_SEQ INCREMENT BY 1 START WITH 1 MAXVALUE 25 MINVALUE 1

SELECT TEST_SEQ.NEXTVAL FROM DUAL;
NextVal
----------
1
....
.........
SELECT TEST_SEQ.NEXTVAL FROM DUAL;
NextVal
----------
20

SELECT TEST_SEQ.CURRVAL FROM DUAL;
CURRVAL
-----------
20

DROP SEQUENCE TEST_SEQ;

Again I am creating the same Sequence

CREATE SEQUENCE TEST_SEQ INCREMENT BY 1 START WITH 1 MAXVALUE 25 MINVALUE 1

SELECT TEST_SEQ.CURRVAL FROM DUAL;
CURRVAL
-----------
20

Eventhough I dropped the sequence and I created it again the currval for the first time is showing the same old incremented sequence value. If I select the NEXTVAL from there onwards the currval will be initialised to new value.

Is this a desired behaviour Or it it a Oracle Bug ??

Your comments are greatly appreciated.

Thanks In Advance,
Sekhar Modem.

[eddyng]

Hi,

I don't know whether this is bug or not, but it seems to be a problem that the sequence is being cached. This is the default behavior, and the default cache size is 20 that explains why the second 'NEXTVAL' returns 20.

You may try to issue a 'COMMIT' after you drop the sequence and test again, if fail, you can try to alter/create the sequence with 'NOCACHE' option.

Eddy Ng

[amar]

Originally posted by s_modem
Again I am creating the same Sequence

CREATE SEQUENCE TEST_SEQ INCREMENT BY 1 START WITH 1 MAXVALUE 25 MINVALUE 1

SELECT TEST_SEQ.CURRVAL FROM DUAL;
CURRVAL
-----------
20

At this point the value TEST_SEQ.CURRVAL is NOT yet defined. How did you get oracle to return a value to you??????

-amar

[s_modem]

Hi,

The Database is Oracle
Version : 8.1.6


I tried using commit after dropping the sequence. But it doesnt work.
I guess this is a bug ??


Your comments are appreciated.

Thanks.
Sekhar Modem.

[akwete]

Hi s_modem

try this: This works for me.

CREATE SEQUENCE TEST_SEQ NOCACHE
START WITH 1
INCREMENT WITH 1
--MAXVALUE 25
NOCYCLE;

note: There is no need for minvalue and even maxvalue. The sequence will start with 1 and increment with 1 until 25 is reached. What do you do after 25?

C. K.
Apps DBA