SQL & PL/SQL

Hi,

From past few days we are facing some weird issue in our application where the sequences seems to be generating duplicate values. Previously I thought that is is not possible, but now it seems to be an issue. Below are the details:

select * from v$version;

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

PL/SQL Release 11.2.0.2.0 - Production

CORE 11.2.0.2.0 Production

TNS for Linux: Version 11.2.0.2.0 - Production

NLSRTL Version 11.2.0.2.0 - Production

We use to insert in a table's column the latest value of sequence which is of course the primary key column of the table. Today we received a unique constraint violation on its primary key column (which is driven by the sequence). Below was the latest value of the PK column

SELECT MAX(PCCURVEDETAILID) FROM T_PC_CURVE_DETAILS;

162636

and below was the latest value of the sequence which was lesser than the PK column

SELECT S_PCCURVEDETAILS.NEXTVAL FROM   DUAL;

162631

We then checked the data in the user_sequences and found the next value of sequence(LAST_NUMBER) to be 162645 which was higher than the current value of sequence:

select * from  user_sequences a where a.sequence_name like 'S_PCCURVEDETAILS';

I then altered the sequence to nocache:

alter sequence S_PCCURVEDETAILS nocache;

select * from  user_sequences a where a.sequence_name like 'S_PCCURVEDETAILS';

SELECT S_PCCURVEDETAILS.NEXTVAL FROM   DUAL;

162632

We also faced the same type of primary key violation issue with another sequence driven table few days before.

I have also checked in our code that the primary key value in the table is being inserted only using the sequence value.

Can anyone please suggest any reason for this issue

Regards

Deepak