Sunday, 10 February 2013

SQL-Oracle11gR2- Issue while inserting in a table using sequence in Oracle 11g Rel 2


While inserting into a table using sequence it does not start with the start with value.

Example:

CREATE SEQUENCE ep_test_seq
START WITH 1
INCREMENT BY 1
MAXVALUE 99999999999
MINVALUE 1
NOCYCLE
NOCACHE
NOORDER;


create table test (a number);

insert into test values (ep_test_seq.nextval );

select * from test;

A


2

Here sequence is started with 2 instead of 1, why because when you attempt to insert a sequence value into a table that uses deferred segment creation, so the first value that the sequence returns will be skipped.

Way to resolve
1.    Disable deferred segment creation of Oracle by setting the initialization parameter DEFERRED_SEGMENT_CREATION to FALSE.
ALTER SYSTEM SET DEFERRED_SEGMENT_CREATION=FALSE

2.     The new clauses SEGMENT CREATION DEFERRED and SEGMENT CREATION IMMEDIATE are available for the CREATE TABLE statement. These clauses override the setting of the DEFERRED_SEGMENT_CREATION initialization parameter.