OraFAQ Forum: SQL & PL/SQL » Sequence skip numbers in oracle

Home » SQL & PL/SQL » SQL & PL/SQL » Sequence skip numbers in oracle (windows , oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Sequence skip numbers in oracle [message #322598]

Sat, 24 May 2008 15:38

piyush_batra
Messages: 2
Registered: May 2008

Junior Member

I am facing some problem with sequences in oracle db.
There is a sequence with a interval of one to autoincrement the sequence.
There is a cache of 10 associated with this sequence.
Now when the application uses this sequence it is observed that sometimes the sequences skip numbers.
like
3000 , 3001, 3002, 3010 , 3011 , 3020 ...
you can see the jump from 3002 to 3010 and also from 3011 to 3020 where some numbers are missing.
I got some info on this ...like this may happen when db instance is shutdown and then restarted then the cache is lost.
But this is not the case with our application as db was not down.
So is there any other way by which the cache data can be lost and sequence skip numbers??

Kindly give your inputs if anyone faced such problem and the solution to overcome this.
Please revert back. Looking forward to your response.

Piyush

Report message to a moderator

Re: Sequence skip numbers in oracle [message #322599 is a reply to message #322598]

Sat, 24 May 2008 15:47

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>So is there any other way by which the cache data can be lost and sequence skip numbers??

ROLLBACK & not commit changes.

Report message to a moderator

Re: Sequence skip numbers in oracle [message #322600 is a reply to message #322599]

Sat, 24 May 2008 15:56

piyush_batra
Messages: 2
Registered: May 2008

Junior Member

thanks for the quick reply
In the code we have not written any rollback related stuff for thsi particular section.
still are there any more scenarios where cache can be lost or sequence can skip numbers ?
Is it sequence skipping related to cache loss or there can be anyother possibilty for such behaviour.

piyush

Report message to a moderator

Re: Sequence skip numbers in oracle [message #322601 is a reply to message #322600]

Sat, 24 May 2008 16:12

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

You can specify NOCACHE when creating your sequence to eliminate some of the gaps. However, in general, Oracle sequences are not expected to be gap-free and gaps should not pose a problem. The important thing is that each sequence is unique.

Report message to a moderator

Re: Sequence skip numbers in oracle [message #322602 is a reply to message #322600]

Sat, 24 May 2008 16:16

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

The following demonstrates how, even without caching, once a nextval is used, if you rollback, it is still incremented.

SCOTT@orcl_11g> CREATE SEQUENCE test_seq
  2  START WITH 1
  3  INCREMENT BY 1
  4  NOCACHE
  5  /

Sequence created.

SCOTT@orcl_11g> CREATE TABLE test_tab (col1 NUMBER)
  2  /

Table created.

SCOTT@orcl_11g> INSERT INTO test_tab SELECT test_seq.NEXTVAL FROM DUAL
  2  /

1 row created.

SCOTT@orcl_11g> COMMIT
  2  /

Commit complete.

SCOTT@orcl_11g> INSERT INTO test_tab SELECT test_seq.NEXTVAL FROM DUAL
  2  /

1 row created.

SCOTT@orcl_11g> ROLLBACK
  2  /

Rollback complete.

SCOTT@orcl_11g> INSERT INTO test_tab SELECT test_seq.NEXTVAL FROM DUAL
  2  /

1 row created.

SCOTT@orcl_11g> COMMIT
  2  /

Commit complete.

SCOTT@orcl_11g> SELECT * FROM test_tab
  2  /

      COL1
----------
         1
         3

SCOTT@orcl_11g>

Report message to a moderator

Re: Sequence skip numbers in oracle [message #322603 is a reply to message #322600]

Sat, 24 May 2008 16:19

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

There are rare instances in which a gap-free sequence is some sort of legal requirement or some such thing. In such cases, the following thread shows how to do that, but causes it to be very slow:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1508205334476

Report message to a moderator

Re: Sequence skip numbers in oracle [message #322609 is a reply to message #322598]

Sat, 24 May 2008 22:39

TheSingerman
Messages: 49
Registered: April 2008
Location: Brighton, Michigan

Member

Quote:

The following demonstrates how, even without caching, once a nextval is used, if you rollback, it is still incremented.

And this is the documented, expected behavior.

Please try to think about the purpose for sequences in the first place. Before there were sequences, the way to get a unique number was to keep a number in a user created table. When you needed to "get the next number", you would lock the table, get the number, do something with it, and then release your lock. While you were trying to do something with the number, everyone else was blocked, waiting for you to release your lock.

Enter sequences. Now, instead of locking a table for the duration of a transaction, you just latch and release the next number in memory (assuming you have it cached). No blocking. Great concurrency. But -- bumping a sequence is not transactional.

Because, if it were, and you could reset the sequence value to whatever it was when you grabbed yours, then one of two things would have to happen:

1) If someone requested the next sequence while you were deciding to rollback, they would have grabbed the next number. And, whoever grabbed the next sequence after you rolled back would also grab that number. Two identical numbers. Can't have that. So...

2) Oracle would have to put a iron-clad bullet-proof lock on the sequence, so no one could get one until you made up your mind about it. Boom, we are back where we started before sequences.

(I leave as an exercise to the reader why having Oracle reset the sequence value after a rollback to the next value after the last task to bump the sequence is an even worse idea than the two above).

As an aside, sequence metadata is kept in sys.seq$. Cached values are kept in memory; when the cache is exhausted sys.seq$ is read, the cache refreshed, and sys.seq$ updated. As with all system tables, it is protected by latches from concurrent updates. And, since there is but one seq$ table in the instance, going nocache with your sequences means increased latching of seq$, and slowing down everyone in the instance (including those who are managing their sequences "correctly".

Did I remember to state at the beginning which side of this religious war (to gap or not to gap sequences) I stand on?

Report message to a moderator

Previous Topic:	Just a little help for the question
Next Topic:	how to grant all normal user previleges in a single statemnt..

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Sep 08 16:13:35 CDT 2025