Home » SQL & PL/SQL » SQL & PL/SQL » sequence gap (merged)
sequence gap (merged) [message #328875] Mon, 23 June 2008 03:34 Go to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,
I am getting one issue around the sequence ,I have created the sequence with

create sequence xxx
.....
....
increment by 1
cache 20
nocycle


my issue is when i am calling the sequence the first number gets as 1 as it STARTWITH is 1 and next it comes as
21,
41,
61,
81......
(ie) it increases by 20 for each increment ,could anyone pls comment on this issue its very urgent


Thanks,
Re: Reg Sequence Cache Problem [message #328877 is a reply to message #328875] Mon, 23 June 2008 03:44 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Don't use forums if it is urgent; hire a consultant.

The seen behavior is caused by the caching of the values. If you use a sequence, sooner or later you will end up with gaps in the numbers used.
This is NOT a bad thing (in 99.9999% of the cases). If people tell you you have to have consecutive ranges, ask them why.
Re: Reg Sequence Cache Problem [message #328878 is a reply to message #328877] Mon, 23 June 2008 03:51 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,
There is any possible way to correct this sequence without the cache problem,if means can u pls provide it

Thanks,
Re: Reg Sequence Cache Problem [message #328881 is a reply to message #328878] Mon, 23 June 2008 04:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
can u pls provide it

can YOU PLEASE don't use IM speak.

Quote:
There is any possible way to correct this sequence without the cache problem

Which problem? I don't see any problem, it is the expected behaviour.

Regards
Michel
Re: Reg Sequence Cache Problem [message #328882 is a reply to message #328877] Mon, 23 June 2008 04:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It's not standard behaviour with cached sequences - normally you should get the next value from the sequence.

This behavious suggests that something is clearing the cached values out between selects?

Is anything like the database being shut down happening between these sequence value selects?

Can you post a cut and paste SQL*Plus session showing this problem happening?
Re: Reg Sequence Cache Problem [message #328890 is a reply to message #328882] Mon, 23 June 2008 04:34 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,

QUOTE
Can you post a cut and paste SQL*Plus session showing this problem happening? 


I could possibly say there is no any errors reg this issue ,my question is

1.why it happens? and purpose for this behaviour

QUOTE
It's not standard behaviour with cached sequences - normally you should get the next value from the sequence.


I agree this statement,i could say this is not the exact behaviour

Thanks,
Re: Reg Sequence Cache Problem [message #328891 is a reply to message #328890] Mon, 23 June 2008 04:40 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I could possibly say there is no any errors reg this issue ,my question is


Yes, you could say that. However, you've not posted nearly enough information for us to be able to give more than superficial help.

What you describe is odd. In cases where people claim that Oracle is doing odd things, I find that generally Oracle is working normally, and the user is doing something odd.

I strongly doubt that you are doing this:
SQL> create sequence seq
  2  increment by 1
  3  cache 20
  4  nocycle;

Sequence created.

SQL> select seq.nextval from dual;

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

SQL> /

   NEXTVAL
----------
         2

SQL> /

   NEXTVAL
----------
         3

SQL> /

   NEXTVAL
----------
         4

and getting the results that you describe above.

Tell us what you are doing to get these results. Describe the problem in more detail.
Reg the sequence gap [message #331062 is a reply to message #328875] Wed, 02 July 2008 04:35 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,
Is there is a possiblity of sequence gap, if means how to overcome this and what are steps to be followed

For me there was issue in the sequence generating in my production DB ,ie

I have created the sequences xxx

create sequence xxx
startwith 1
increment by 1
maxval  9999999
cache  20
nocycle 


for ex:

sql>select xxx_seq.nextval from dual


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


1 row selected.

sql>select xxx_seq.nextval from dual

   NEXTVAL
----------
         21

sql>select xxx_seq.nextval from dual

   NEXTVAL
----------
         41


Here the sequences generated has been increasing by 20 for every increment ,so pls help on this and provide a needful help

Thanks,
Re: Reg the sequence gap [message #331065 is a reply to message #331062] Wed, 02 July 2008 04:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Same question, same answer as when you posted it in:
http://www.orafaq.com/forum/m/328875/102589/#msg_328875

Regards
Michel
Re: Reg the sequence gap [message #331081 is a reply to message #331062] Wed, 02 July 2008 05:02 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
As we asked you before, please show us a cut/paste from SQL*Plus showing this happening. Please include your Oracle version number as well.

I'm fairly sure that you are missing something significant out of this description, as this is non-standard behaviour.

I know for a fact that what you have pasted isn't what you executed, becuase of the number of error I had to fix when I copied it into Sql*Plus

I get this:
SQL> create sequence xxx
  2  start with 1
  3  increment by 1
  4  maxvalue 9999999
  5  cache  20
  6  nocycle;

Sequence created.

SQL> select xxx.nextval from dual;

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

SQL> select xxx.nextval from dual;

   NEXTVAL
----------
         2

SQL> select xxx.nextval from dual;

   NEXTVAL
----------
         3
Re: Reg the sequence gap [message #331083 is a reply to message #331065] Wed, 02 July 2008 05:04 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,
can i know why it occurs so pls help on this ,this issue along the Production DB so possibly no way to play on it

thanks,
Re: Reg the sequence gap [message #331100 is a reply to message #331083] Wed, 02 July 2008 05:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Without more information I don't think there is much we can do to help you.

Try answering some of the following questions: What sort of application are you getting this in - client/server, Web based, serverside batch processing....

Can you reproduce it on your dev/test box

Does it happen when you select from the sequence in SQL*Plus.

If you won't help us, we can't help you.
Re: Reg the sequence gap [message #331149 is a reply to message #331100] Wed, 02 July 2008 07:02 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,
It occurs along the client side ,but in our local and UAT server it doesnt happens

And it happens when the select query passes

since its Production DB we are unable gather the information,so pls tell why it occurs ,whetehr it could be OS side problem or anything else pls comment on this issue

Thanks,
Re: Reg the sequence gap [message #331155 is a reply to message #331062] Wed, 02 July 2008 07:32 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
ram anand wrote on Wed, 02 July 2008 05:35

create sequence xxx
startwith 1
increment by 1
maxval  9999999
cache  20
nocycle 


for ex:

[code]sql>select xxx_seq.nextval from dual


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


1 row selected.

sql>select xxx_seq.nextval from dual

NEXTVAL
----------
21




Liar liar pants on fire. You faked your session. I do not believe anything you are saying. You create a sequence called XXX, but you show us sequence XXX_SEQ. You do not have a semicolon or slash to show the statement executed. Now please stop wasting our time.
Re: Reg the sequence gap [message #331157 is a reply to message #331149] Wed, 02 July 2008 07:38 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
What sort of application are you getting this in - client/server, Web based, serverside batch processing....


On the Local and UAT databases, are you running the same application?

Also, what do you mean by 'And it happens when the select query passes'

What language is this query being executed from?

You've got about 2 more questions before I get bored of this and move on, so I'd strongly recommend that you provide a decent description of the structure of your application, how the problem is spotted, how many sequences it occurs in, and so on and so forth.

from the information you have provided so far, I am unable to determine the cause of the problem,.
Re: Reg the sequence gap [message #331158 is a reply to message #331155] Wed, 02 July 2008 07:42 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,
Its just an example only ,i need the reason why it occurs

Thanks,
Re: Reg the sequence gap [message #331162 is a reply to message #331158] Wed, 02 July 2008 08:02 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
We seem to be having a substantial communication problem here.

I shall attempt to express myself more clearly.

Given the information you have provided, I cannot say why this is happening.

If you want me to be able to tell you why this is happening, you will have to provide me with more information.

All I have to work with is your claim that every time you get the next value from this sequence, the increment from the previous verision is the sequence cache value.

You have also said that you are unable to reproduce this problem as you cannot run tests on the live Db.

I therefore conclude that all the SQL and results that you posted at the start of this thread are fictional.

The only thing I know of that will cause a sequence to increment like that is the database crashing in-between the two SELECTs, but I'm sure a clever chap like you would have mentioned something minor like that, wouldn't you.

You have one more attempt to provide enough information, after which You're On Your Own.
Previous Topic: add the unconsildate amount to match with consolidate amount
Next Topic: Select one table base on value of another table
Goto Forum:
  


Current Time: Wed Dec 07 16:50:50 CST 2016

Total time taken to generate the page: 0.24381 seconds