Deferred Segment Creation under Oracle 11.2 and Sequences

Posted by Dirk Nachbar on Tuesday, January 25, 2011
An ex-colleague (Christian Zuberbühler) of me pointed me to a strange behavior under Oracle 11.2 with sequences.
So I had a quick look at it, following show case:

create sequence seq_t1_id start with 1;
create table t1 (id number, col1 varchar2(20));
insert into t1 values (seq_t1_id.nextval, 'Test Value expected 1');
commit;
select * from t1;
ID          COL1
---------- --------------------
2           Test Value expected 1

Nice :-) Normally everybody is expecting under the column ID the value 1.

As my test table is created under Oracle 11.2 the default setting for the new feature deferred_segment_creation is true. Therefor the insert command is facing an ORA-14403 internally as the affected table doesn't have any segment. A new segment will be created for my test table and the insert command will be re-run, so the inserted value from the used sequence will be one value higher.
The internally faced ORA-14403 can easily proven with the event 14403. Just set the event 14403 before you execute the insert statement:

create sequence seq_t1_id start with 1;
create table t1 (id number, col1 varchar2(20));
ALTER SESSION SET EVENTS '14403 trace name errorstack level 3'; 
insert into t1 values (seq_t1_id.nextval, 'Test Value expected 1');
commit;

In the corresponding alertlog you will find then something similar like this:

Tue Jan 25 16:54:19 2011
Errors in file e:\oracle\diag\rdbms\xxx\xxx\trace\xxx_ora_6072.trc:
ORA-14403: cursor invalidation detected after getting DML partition lock
Tue Jan 25 16:54:26 2011
Trace dumping is performing id=[cdmp_20110125165426]

According to Oracle this is not a bug, its an expected behavior from Oracle 11.2 going. The recommended solution from Oracle is "deferred_segment_creation=FALSE" . . .
Categories: