1 2 Previous Next 17 Replies Latest reply: May 2, 2012 1:45 AM by Girish Sharma Go to original post RSS
      • 15. Re: Sequence out of sync
        user1016739 wrote:
        Here is an example of sequence definition, insert statement and what two sessions execute concurrently:

        sequence definition:
        CREATE SEQUENCE <schema_name>.tab1_id_sq START WITH 1 increment by 1 nomaxvalue order cache 20;

        insert statement:
        INSERT INTO <schema_name>.tab1 (id) VALUES (<schema_name>.tab1_id_sq.nextval);

        Let's say there are two sessions, S1 and S2. If S1 is running "INSERT INTO schema1.tab1 (id) VALUES (schema1.tab1_id_sq.nextval);", then S2 is running "INSERT INTO schema2.tab1 (id) VALUES (schema2.tab1_id_sq.nextval);", so they don't access the same table or the same sequence, but two tables and two sequences in different schemas.
        If this is correct, there shouldn't be any sequence number collisions happening. How about posting a proof of that sequence numbers are overshooting the already existing PK values.

        • 16. Re: Sequence out of sync
          I've seen the max(id) of a table is 465, but select schema.seq.nextval from dual returns 93.

          However, this isn't the only problem. When we see PK violation errors, sometimes those sequence numbers are actually higher than max(id). In that case, we have to stop concurrent sessions that are inserting data, wait for a while, and run one process at a time, then everything works fine.
          • 17. Re: Sequence out of sync
            Girish Sharma
            Truly speaking there is no issue at your database level, I can bet there is flaw in your application coding. Some application or part of application is not using sequence to insert the primary key column.

            So in short, with the help of triggers and/or auditing their inserts, you can prove that the problem is in the application, and not in the database.

            You are using sequence for primary key, it means be rest assure Oracle will generate unique numbers (not gap free,always increasing) for the PK.

            Some scenarios might be the reason for this.
            You insert a row using the sequence. You insert a new row using the same number again without fetching it from NEXTVAL (e.g. using currval instead of nextval, or not updating a variable that holds the new ID,...)

            Somebody else already inserted an ID without using the sequence

            A variation is when you import data from another database and resetted the sequences

            There is some test data in your tables. The test data was inserted in a high ID range to separate it from the real data. Your sequence just reached this range.

            Source:Sequence Last Number doesn't match with currval

            Girish Sharma
            1 2 Previous Next