Sequence id is not properly inserted

Question

I have created a sequence but its not inserting ids in sequence order.

For Ex:

First I have created one set of record seq number generated as 1, 2, 3, 4
Again I have created another set of records seq started from 8, 9, 10
For 3rd time I have created another set of records seq id got generated as 5, 6, 7 (which is not correct, I want the seq id to be continued as 11, 12, 13)

So 5, 6, 7 is wrong, I need 11, 12, 13 to be generated

What's wrong in my below sequence create query?

CREATE SEQUENCE "LEASE_REPAYMENT_SEQ" 
MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1
START WITH 146724 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE GLOBAL ;

Nothing is wrong with your sequence, but if you bypass it during INSERT then obviously it will not be incremented — , Jun 15 '21 at 15:13
Are you working on `RAC` with two different sessions? Are yous numbers only *examples* of a wrong order? — Marmite Bomber, Jun 15 '21 at 15:49

score 3 · Answer 1 · answered Jun 15 '21 at 15:43

If you check the sequence definition, you'll see that you define it as NOORDER.

The Oracle Documentation says

Specify NOORDER if you do not want to guarantee sequence numbers are generated in order of request. This is the default.

So what you see is an expected bahaviour and I assume your database is a RAC instance (as this effect can be observed on RAC only).

Having said that, there are good reasons to allow this small dis-order of the assigned IDs (which is caused by the caching as each RAC instance gets it own cache size to work with).

The positive side of this apprach is that there is no need to synchronize the sequence between the instances - a task that could produce a big overhead.

Sequence id is not properly inserted

1 Answers1