Resetting Sequence  

Posted by Mawahid


Some refresh or migration happened and because of that, users seeing their sequence last number is reset to 1 or some other value smaller to max(id) of the sequence table, and hence they are not able to insert the data.


Suppose there is a column in a table(TAG) called tag_id, which should be unique. Some applications designed in such a way that data will get inserted through sequence number or similarly. So, in general in such case, sequence - last_number should be greater than max(tag_id), in order to sucussful insertion of data.

Here the last_number of sequence is reset to 1 because of which users unable to insert data, as it throws tag_id with value 1 is already exists.

            select sequence_name,last_number from dba_sequences where sequence_name='<sequence_name>';
            select max(tag_id) from TAG;

Here TAG is the sequence table.

Check the metadata of the sequence, and found increment is set to 1.

            alter sequence <sequence_name> increment by 26589;  - This is the vale greater than max(tag_id) or put the value which match with production.

Now check its nextval

            select <sequence_name>.nextval from dual;

Now change the increment value to 1 as before, in order to match the metadata.

            alter sequence <sequence_name> increment by 1;
            alter sequence <sequence_name> increment by -2;   - We can also give minus values in order to set accordingly.

Now the last_number would be set as we required.

select sequence_name,last_number from dba_sequences where sequence_name='<sequence_name>';

This entry was posted on Monday, October 18, 2010 at 9:18 PM . You can follow any responses to this entry through the comments feed .


Post a Comment