Short and sweet way to increase Sequence value to be higher than the max value in the table

Many times during the development process, there is a need to copy /export/backup table data between environments or stages. While it is easy to export the data using SQL inserts, it is also very easy to forget the sequences related to the tables.
Sometimes, this practice results in DUP_VAL_ON_INDEX exception (ORA-00001) when you use NEXTVAL in your insert. The tables may already include high values but the sequence is lower than the max values in the table.
Therefore it is important to remember to increment the sequences to a value higher than the max value in the table.

The most common way to increase the sequence value to the next value in the table is to:

1) alter the sequence increment to the difference between the current value of the sequence and the max value in the table.

ALTER SEQUENCE sequence-name INCREMENT BY 500;

2) Issue a dummy nextval request

SELECT sequence-name.NEXTVAL FROM dual;

3) Alter the sequence increment value back to the original increment

ALTER SEQUENCE sequence-name INCREMENT BY 1;

Another option of course is to drop the sequence and recreate it with the required value but this may invalidate objects referencing to it. Also, it will force you to re-apply all grants and permissions.
Both above methods will work but not everyone have permissions to change the sequence. In addition, DDL operations are always risky. So, If you are looking for a short and sweet (or quick and dirty) way to increase the sequence value, look no further.

SELECT  level, sequence-name.NEXTVAL
FROM  dual 
CONNECT BY level <= (SELECT MAX(column-using-the-SEQUENCE  ) FROM table-name);
Share on RedditEmail this to someoneShare on Google+Share on LinkedInShare on FacebookDigg thisTweet about this on Twitter

Leave a Reply

Your email address will not be published. Required fields are marked *