Duplicate Values in a Sequence

I was called immediately to look into GL transactions failure, the issue was GL transaction were failing due to constraint violation. After closely looking into the error then came to conclusion that primary key posting_id in a table was primary key and this value is generated by sequence and its generating the duplicate values.


How can the sequence generate the duplicate values started looking into it.. hence my finding on this issue are.. when and how duplicate values are generated

1. Values can be duplicate if the CYCLE clause is used at the time of creating sequence, what it means when the high value is reached, it shall start from min-value resulting in duplicate values
2. the problem is that someone either accidentally used a different sequence to generate a few primary keys
3. Somehow have set some primary key manually
4. or copied rows from other environment without changing the primary key or updating the sequence.

Looking at above scenario my case fits in case 1.

Now the action plan to fix

1.extract the definition from dba_sequences
2.Take the invalid object count(why becoz dependents objects in db will be invalid need to compile them later)
3.Create the sequence with nocycle clause
4.compile invalids

I also learned two more things.

1.>>>>The current value of Sequence is not stored in dba_sequences whereas its stored in v$_sequences.nextvalue. DBA_Sequences store the cache values so it doesn't reflect the correct value of sequence.

Query

SQL> select demo.nextval from dual;

NEXTVAL
----------
2

=>Current Value of sequence is 2

Querying value from dba_sequences

SQL> select sequence_owner,sequence_name,last_number
2 from dba_sequences where sequence_name='DEMO';

SEQUENCE_OWNER SEQUENCE_NAME LAST_NUMBER
------------------------------ ------------------------------ -----------
SYS DEMO 21

>SEE the value of last_number.

Exact value can be retrieved

SQL> select sequence_owner,sequence_name,nextvalue
2 from v$_sequences where sequence_name='DEMO';

SEQUENCE_OWNER SEQUENCE_NAME NEXTVALUE
-------------------- -------------------- ----------
SYS DEMO 2



2>>>> Difference in sequence cant be avoided because of instance failure, transaction failure, rollback.

Example lets do instance failure and verify

SQL> select sequence_owner,sequence_name,last_number
2 from dba_sequences where sequence_name='DEMO';

SEQUENCE_OWNER SEQUENCE_NAME LAST_NUMBER
-------------------- -------------------- -----------
SYS DEMO 21

SQL> select demo.nextval from dual;

NEXTVAL
----------
3

SQL> shut abort
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 564957184 bytes
Fixed Size 1384956 bytes
Variable Size 201330180 bytes
Database Buffers 356515840 bytes
Redo Buffers 5726208 bytes
Database mounted.
Database opened.

SQL> select demo.nextval from dual;

NEXTVAL
----------
21

I read on asktom - > three things cant be avoided

1.Taxes
2.Death
3.Gap in sequences.

Hence beware of Gaps and if you need strictly in order then use some other logic as well.