Consider a sequence named "MY_SEQ" which has been created to generate automatic numbers. Somebody, by mistake, has tried to access the sequence from SQL*Plus and consequently the value has been increased to e.g. 32. Our objective is now to reset the value to 10 so that next created number will have a value of 11.
Find out the INCREMENT BY parameter for the sequence with the following query
WHERE sequence_name = 'MY_SEQ' ;
This will return 1. It is not always necessary to run the above query to find out the INCREMENT BY value. You can find it out by looking at the column data for which the sequence is used.
Alter the sequence with a negative INCREMENT BY value to count backwards
ALTER SEQUENCE my_seq INCREMENT BY -1;
Run the PL/SQL block below to reset the sequence value to a desired number:
IF (temp = 10) THEN
WHEN OTHERS THEN
Again reset the INCREMENT BY value to original
ALTER SEQUENCE my_seq INCREMENT BY 1;
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/936/viewspace-60596/，如需转载，请注明出处，否则将追究法律责任。