because the oracle doesn't provide the feature to alter a sequence's currval, so there're some other ways to alter the currval.First method: drop then recreate it with the exact start number. maybe it's the easiest method. but if you have any grants, they will have to be recreated, and any dependent objects will need to be re-compiled. That's why the other method was suggested. It's really a matter of choice, but if you don't know all the repercussions, dropping any object can be dangerous. Second method: alter sequence seq_test increment by SubtractValue; select seq_test.nextval from dual; alter sequence seq_test increment by 1;NOTE: SubtractValue is a integer of (new value want to set subtract the old currval), its value can be any positive or negative integer. For example, if you want to set the currval from 5000 to 5000000, then use (5000000 - 5000), if you want to set the currval from 5000000 to 100, then use (100 - 5000000), etc. The following is the samples for the exact cases:from 5000 to 5000000: alter sequence seq_test increment by 4995000; select seq_test.nextval from dual; alter sequence seq_test increment by 1;from 5000000 to 100 alter sequence seq_test increment by -4999900; select seq_test.nextval from dual; alter sequence seq_test increment by 1; wish the solutions helpful to you. :->
hi blackest(卧松云) : I think your statement "ALTER SEQUENCE 名称 minvalue value" can be helpful for the small value to big value, but it is also helpful for big value to small value?
drop then recreate it with the exact start number.
maybe it's the easiest method. but if you have any grants, they will have to be recreated, and any dependent objects will need to be re-compiled.
That's why the other method was suggested. It's really a matter of choice, but if you don't know all the repercussions, dropping any object can be dangerous. Second method:
alter sequence seq_test increment by SubtractValue;
select seq_test.nextval from dual;
alter sequence seq_test increment by 1;NOTE: SubtractValue is a integer of (new value want to set subtract the old currval), its value can be any positive or negative integer. For example, if you want to set the currval from 5000 to 5000000, then use (5000000 - 5000), if you want to set the currval from 5000000 to 100, then use (100 - 5000000), etc.
The following is the samples for the exact cases:from 5000 to 5000000:
alter sequence seq_test increment by 4995000;
select seq_test.nextval from dual;
alter sequence seq_test increment by 1;from 5000000 to 100
alter sequence seq_test increment by -4999900;
select seq_test.nextval from dual;
alter sequence seq_test increment by 1;
wish the solutions helpful to you. :->
I think your statement "ALTER SEQUENCE 名称 minvalue value" can be helpful for the small value to big value, but it is also helpful for big value to small value?