Difference in oracle 11.2.0.1.0 and oracle 11.2.0.2.0 while inserting a value into the table using sequence

Question

In Oracle 11.2.0.1.0:

1) I created a table.

create table m1(id number(5,2), version number(5,2), primary key (id));

2) I created a sequence.

CREATE SEQUENCE m1_id_sq;

3) I inserted values into the table.

insert into m1(id, version) values (m1_id_sq.nextval, 1);

4) output.

id version

-------------

2 1

*I understand the reason for id=2 is due to deferred_segment_creation feature introduced from 11.2.0.1.0 onwards.

*I created an user instance in oracle and I ran the above three commands. Not as a master.

Now I follow the same steps

in Oracle 11.2.0.2.0,

but the output I got is,

id version

-------------

1 1

Please explain why the id=1 in oracle 11.2.0.2.0 whereas id=2 in oracle 11.2.0.1.0. Great thanks!

Answer 1

The problem may have to due with the fact that NOORDER is the default with Oracle Sequences, especially if you're running a RAC environment.

http://docs.oracle.com/cd/B12037_01/server.101/b10759/statements_6014.htm

I've learned that with Sequences, if I want to guarantee that they be sequential I usually have to add the following keywords when creating the sequence:

CREATE SEQUENCE m1_id_sq ORDER NOCACHE;

Edit to refer to above comments:

As noted by Alex Poole in the comments above:

"This shouldn't really matter anyway - you'll get gaps in sequences for other reasons so you shouldn't rely on it starting with 1"

The NOORDER being the default for sequences explains this issue.

Alex Poole also noted a known issue: Oracle Note 1050193.1 (requires an Oracle Support account) related to an issue with deferred_segment_creation=TRUE.

ThinkJet also refers to the following articles that show that early versions of Oracle had some issues with sequences and deferred segments:

https://web.archive.org/web/20150914162717/http://orawin.info/blog/2010/04/25/new-features-new-defaults-new-side-effects/

https://web.archive.org/web/20151004175547/http://orawin.info/blog/2011/11/17/new-defaults-old-side-effects/