Power to Build

Home » Misc » Oracle Sequences Contd… Resynching it

Oracle Sequences Contd… Resynching it

​I posted in detail about Oracle Sequences before. Another user was having an issue. One of the screen failed to update data and it showed the primary key violation. After looking at the message, I realized it could be another Sequence synchronization issue.

When you get this kind of error, first thing to do is to check the maximum ID value in the table and the next value for the sequence. If the sequence.nextval < max(table.id), then it will generate primary key violation.

​For e.g., to check on the id and sequence for an employee table,​

-- max_id
SELECT Max(​employee_id) FROM ​employee;
-- max_sequence
SELECT ​employee_seq.nextval FROM dual;

If the max_sequence < max_id, then you will have to bump up the Sequence to a value > max(id) in the table. To do this, there is no straightforward way, except to increment the sequence by the difference. Here are the steps:

1. Find the difference between the 2 – max_id – max_sequence. Let’s call it

2. Now change the increment value of the sequence to DIFF.
ALTER SEQUENCE employee_seq INCREMENT BY ;

3. Select nextval from Sequence to push the sequence in synch with Max(id).
SELECT employee_seq.nextval FROM dual;

4. You cannot leave it like that – because the increment by is now , every time the nextval is called. Change the “Increment by” back to 1.
ALTER SEQUENCE employee_seq INCREMENT BY 1;

5. Select nextval from sequence one more time, to bump the sequence past the max(id).
​​SELECT employee_seq.nextval FROM dual;

This will bring the sequence back in synch. Of course, you will have to find out, why the sequence fell out of sync in the first place.


1 Comment

  1. […] 1. Otherwise, you will be surprised by the next value you get in the database!! See my earlier post here for more about resetting the […]

Comments, please?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: