Deferred Segment Creation under Oracle 11.2 and Sequences
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:
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:
In the corresponding alertlog you will find then something similar like this:
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" . . .
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" . . .
