Pages

18 December 2007

Sequence In Oracle RAC System might not generate numbers In a sequence!

Today, after migration to RAC system, i
realized that sequence numbers(usally for generating primary keys) are
not generated in a sequence. Applications that read historical data
would fail because of that. Before RAC system ;
ID INSERT_DATE
1 16.12.2007 16:21:00
2 16.12.2007 16:21:10
3 16.12.2007 16:23:49

As you see, insert date of 82 is before than insert date of 81. There is a mismatch with insertion dates and sequences. Then i read Oracle documentation:

ORDER
Specify ORDER to guarantee that sequence numbers are
generated in order of request. This clause is useful if you are using
the sequence numbers as timestamps. Guaranteeing order is usually not
important for sequences used to generate primary keys.ORDER is necessary only to guarantee ordered generation if you are using Oracle Database with Real Application Clusters. If you are using exclusive mode, sequence numbers are always generated in order.
NOORDER Specify NOORDER if you do not want to guarantee sequence numbers are generated in order of request. This is the default.

So, in order to keep numbers in an order, i altered the sequence and the problem was solved: