Home » SQL & PL/SQL » SQL & PL/SQL » Sequence (oracle,9i,Linux)
Sequence [message #311910] Mon, 07 April 2008 06:25 Go to next message
sundarfaq
Messages: 235
Registered: October 2007
Location: Chennai
Senior Member
Hi,

My scripts:

DROP SEQUENCE TEST_SEQ;

CREATE SEQUENCE TEST_SEQ
START WITH 1
INCREMENT BY 1
MAXVALUE 999999999
MINVALUE 1
CYCLE
CACHE 20
NOORDER;

ALTER SEQUENCE TEST_SEQ
INCREMENT BY 124;


select test_seq.nextval from dual;


select * from user_sequences;

output:

LAST_VALUE
__________
2604

Actually,the last_value is 248 but it shows a 2604.
Why it comes last_vlaue is 2604?
Re: Sequence [message #311915 is a reply to message #311910] Mon, 07 April 2008 06:32 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
what is 20 times 124? what is the difference between that value and 2604?
Re: Sequence [message #311928 is a reply to message #311910] Mon, 07 April 2008 07:18 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> Actually,the last_value is 248 but it shows a 2604.
How did you get 248? From a table?

I suppose, somebody restarted the DB, so all sequence CACHE values were lost. It is nicely described in documentation in CREATE SEQUENCE Statement:
Quote:
If a system failure occurs, all cached sequence values that have not been used in committed DML statements are lost. The potential number of lost values is equal to the value of the CACHE parameter.
Re: Sequence [message #311929 is a reply to message #311910] Mon, 07 April 2008 07:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I checked your previous messages.
It would nice if you:
- follow the rules and format your post
- thanks or give any feedback to those that help you. At least from time to time.

Regards
Michel
Re: Sequence [message #312053 is a reply to message #311910] Mon, 07 April 2008 20:41 Go to previous messageGo to next message
bibsdash
Messages: 47
Registered: April 2008
Member
Just a thought.
Is any other program or script is using this sequence?
Re: Sequence [message #312163 is a reply to message #312053] Tue, 08 April 2008 02:25 Go to previous message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
bibsdash wrote on Tue, 08 April 2008 02:41
Just a thought.
Is any other program or script is using this sequence?


Of no consequence.
Maybe this will help:
SQL> create sequence tst1 start with 1 increment by 1 cache 10;

Sequence created.

SQL> select last_number from user_sequences where sequence_name = 'TST1';

LAST_NUMBER
-----------
          1

SQL> SELECT tst1.NEXTVAL FROM dual;

   NEXTVAL
----------
         1

SQL> select last_number from user_sequences where sequence_name = 'TST1';

LAST_NUMBER
-----------
         11
Previous Topic: finding primary key
Next Topic: DBA privileges
Goto Forum:
  


Current Time: Sun Dec 11 04:11:05 CST 2016

Total time taken to generate the page: 0.06758 seconds