Oracle Sequence Usage

Example 1: Creating a sequence:
CREATE SEQUENCE ABC INCREMENT by 1 START with 1 MAXVALUE 9999999999 nocycle NOCACHE;

Syntax explanation
CREATE SEQUENCE sequence Name
[INCREMENT by N]--1,
[START with N]--2,
[{Maxvalue/minvalue n| Nomaxvalue}]--3,
[{cycle| Nocycle}]--4,
[{CACHE n| NOCACHE}]; --5,

which
1. INCREMENT by is used to define the step size of the sequence (growth), and if omitted, the default is 1, and if a negative value is present, the values of the sequence are decremented by this step.

2, START with defines the initial value of the sequence (that is, the first value produced), the default is 1.

3. MAXVALUE defines the maximum value that the sequence generator can produce.
Option Nomaxvalue is the default option, which means that there is no maximum definition, at which point the system can produce a maximum value of 10 for the increment sequence, and the maximum value for the descending sequence is-1.
MINVALUE defines the minimum value that the sequence generator can produce.
Option Nomaxvalue is the default option, which means there is no minimum definition, and for a descending sequence, the minimum value that the system can produce is 10 of the 26 square, and for the increment sequence, the minimum value is 1.

4, cycle, and nocycle indicate whether a loop occurs when the value of the sequence generator reaches the limit value. The cycle represents the loop, and the nocycle represents no loops.
If the loop is, it loops to the minimum when the increment sequence reaches its maximum value, and to the maximum value when the descending sequence reaches the minimum value.
If you do not loop, the error occurs when the limit value is reached and the new value continues to be generated.

5. Cache (buffer) defines the size of the memory block that holds the sequence, which defaults to 20. NoCache indicates that the sequence is not buffered in memory.
Memory buffering of sequences to improve performance of sequences

Example 2: Deleting a sequence:
DROP SEQUENCE sequence name;

Example 3: Gets the current and next value of the sequence:
SELECT sequence name. Currval from dual; --Gets the current value of the sequence
SELECT sequence name. Nextval from dual; --Gets the next value of the sequence

Example 4: Viewing a sequence
The data dictionary user_objects can be used to view the user-owned sequences.
The Data dictionary user_sequences lets you view the settings for a sequence.

SELECT * from User_objects;
SELECT * from User_sequences;

Example 5: Modifying a sequence

have alter any SEQUENCE permission to change SEQUENCE. You can alter all sequence parameters except start to. If you want to change the start value, you must drop sequence and then re-create.

such as: Alter sequence seqtest MaxValue 9999999;

See also: http://www.cnblogs.com/linjiqin/archive/2011/08/30/2159301.html

Http://www.cnblogs.com/hyzhou/archive/2012/04/12/2444158.html

Oracle Sequence Usage