Oracle modified sequence (Sequence) Start Value method

Oracle modified sequence (Sequence) Start Value method

The Oracle sequence (Sequence) is primarily used to generate serial numbers, which are often used in applications, especially as ID values, which are used to make table primary keys.

However, sometimes when you need to modify the sequence initial value (start with), some colleagues use this statement to modify: Alter sequence Sequencename START with xxxxxxxx. However, in Oracle DB, there is no such syntax for modifying a sequence. Here are a few ways to modify:

1. Delete the sequence first and then recreate it. But this is not the point to be talked about at the moment. This method is relatively violent, but also more convenient, if the sequence is used, it will affect the normal use of the application.

2. Write a loop to add. If you need to add 500, write a loop to run 500 times for select Sequence_name. Nextval from DUAL;

3. Use increment by to modify the initial value.

For example: If the sequence name is Seq_test, the initial value is 13, and now you want to set the initial value to 1013,increment by value: 1000 (1013-13)

1) Execution: ALTER SEQUENCE seq_test INCREMENT by 1000;

2) Execution: SELECT seq_test. Nextval from DUAL;

3) Execution: ALTER SEQUENCE seq_test INCREMENT by 1;

The modification is complete.
Here is an example of an operation:

Oracle modified sequence (Sequence) Start Value method