How to modify sequence in oracle

Method: 1. Use the DROP SEQUENCE statement to delete the sequence, and then use the Create sequence statement to create a new one; 2. Use Increment By to modify the initial value of the sequence, and the syntax is "ALTER SEQUENCE...INCREMENT BY value".

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

How to modify sequence in oracle

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

However, sometimes when it is necessary 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 sequences. Here are several modification methods:

1. Delete the sequence first, and then create it again.

This method is more violent and more convenient. If the sequence is in use, it will affect the normal use of the application.

If no conditional statement is added, the default sequence format created is as follows:

Semantics:

INCREMENT BY: Specify the sequence growth step long. Can be positive (ascending order) or negative integer (descending order), but cannot be 0. Maximum accuracy 28.

START WITH: Specify the starting number of the sequence. Defaults to the sequence minimum.

MAXVALUE: Specifies the maximum value of the sequence. Maximum 28 bits. Must be greater than or equal to the starting value and greater than or equal to the minimum value of the sequence.

NOMAXVALUE: No maximum value (actually 10^27 or -1). default

MINVALUE: Specifies the sequence minimum value.

NOMINVALUE: No minimum value (actually 1 or -10^26). Default

CYCLE: Specifies that the sequence will continue to be generated from scratch after reaching the maximum or minimum value.

NOCYCLE: No cycle generation. Default.

CACHE: Specify the number of sequence values ​​pre-allocated in the database memory for quick retrieval. The minimum cache value is 2.

To delete a sequence, you must have the drop any sequence permission

Syntax:

##Example:

DROP SEQUENCE oe.customers_seq;

2. Modify the initial value through Increment By.

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, the Increment By value is: 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;

Recommended tutorial: "

Oracle Video Tutorial"

The above is the detailed content of How to modify sequence in oracle. For more information, please follow other related articles on the PHP Chinese website!