Home » RDBMS Server » Server Administration » jumping sequences (Oracle 10g Rel. 2, Windows Server 2003 x64)
jumping sequences [message #352510] Wed, 08 October 2008 02:11 Go to next message
TeiMar71
Messages: 7
Registered: February 2001
Location: AUT
Junior Member
Hi,

sometimes sequences on my oracle rdbms are raising the values by 20.

I can remember a note that i read some years ago. This note said, that after a db crash or shutdown/startup or recovery sequences are raised by 20.

But in my case the sequence raises without any of these actions.

Can anyone explain this behavior!?!?!?

Regards
Mario
Re: jumping sequences [message #352517 is a reply to message #352510] Wed, 08 October 2008 02:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are you in RAC?
What is your increment value?
Does anyone recreate the sequence?

Regards
Michel

[Updated on: Wed, 08 October 2008 02:37]

Report message to a moderator

Re: jumping sequences [message #352522 is a reply to message #352517] Wed, 08 October 2008 03:08 Go to previous messageGo to next message
TeiMar71
Messages: 7
Registered: February 2001
Location: AUT
Junior Member
No, i'm not in a RAC!
all sequences increment by 1!

for 2 sequences i know they are not recreated, because they're mine ...

Regards
Mario
Re: jumping sequences [message #352525 is a reply to message #352522] Wed, 08 October 2008 03:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How did you see the sequence numbers jumped?

Regards
Michel
Re: jumping sequences [message #352548 is a reply to message #352525] Wed, 08 October 2008 06:29 Go to previous messageGo to next message
TeiMar71
Messages: 7
Registered: February 2001
Location: AUT
Junior Member
i have a little program that inserts rows in a table every 5 minutes. the time is continous but not the id. sometimes there is a step by 20. but it used to last a long time until i found this out.

Regards
Mario
Re: jumping sequences [message #352559 is a reply to message #352548] Wed, 08 October 2008 06:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post the test case you executed and we can reproduce including the create sequence...

Regards
Michel
Re: jumping sequences [message #352566 is a reply to message #352559] Wed, 08 October 2008 06:58 Go to previous messageGo to next message
TeiMar71
Messages: 7
Registered: February 2001
Location: AUT
Junior Member
this is the script of the table where this happens :

create table mylog ( id number, startdate date, text varchar2(1000));
create sequence seq_mylog increment by 1 start with 1;

create or replace trigger TMYLOG
before insert on MYLOG
for each row
begin
select seq_mylog.nextval into :new.id from DUAL;
end;
/


every 5 minutes starts a program that inserts a row using the following statement :
insert into mylog (startdate, text) values (sysdate, 'prog started');

Regards
Mario
Re: jumping sequences [message #352570 is a reply to message #352566] Wed, 08 October 2008 07:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No commit?

Regards
Michel
Re: jumping sequences [message #352586 is a reply to message #352570] Wed, 08 October 2008 08:33 Go to previous messageGo to next message
TeiMar71
Messages: 7
Registered: February 2001
Location: AUT
Junior Member
no because the .NET-Framework makes the commit for me!

Regards
Mario
Re: jumping sequences [message #352598 is a reply to message #352586] Wed, 08 October 2008 09:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Copy and paste the content of the table.
Check if there is not something that alter sequence cache (trigger, job).

Regards
Michel
Re: jumping sequences [message #352605 is a reply to message #352598] Wed, 08 October 2008 09:47 Go to previous messageGo to next message
TeiMar71
Messages: 7
Registered: February 2001
Location: AUT
Junior Member
that's it ... i think i should set all sequences to NOCACHE !

Regards
Mario
Re: jumping sequences [message #352615 is a reply to message #352605] Wed, 08 October 2008 10:43 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
TeiMar71 wrote on Wed, 08 October 2008 16:47
that's it ... i think i should set all sequences to NOCACHE !

Regards
Mario

This was not my question but you could do this and if course you will have overhead and possibly waits on this sequence.

Regards
Michel
Previous Topic: db upgrade
Next Topic: How the HWM move?
Goto Forum:
  


Current Time: Thu Dec 08 19:54:59 CST 2016

Total time taken to generate the page: 0.09356 seconds