Problem:
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.
Resolution:
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;
or
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>';
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.
Resolution:
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;
or
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
.