The oracle docs note the syntax for the Oracle CREATE SEQUENCE function as follows:
CREATE SEQUENCE [ schema. ]sequence
[ { INCREMENT BY | START WITH } integer
| { MAXVALUE integer | NOMAXVALUE }
| { MINVALUE integer | NOMINVALUE }
| { CYCLE | NOCYCLE }
| { CACHE integer | NOCACHE }
| { ORDER | NOORDER }
]
[ { INCREMENT BY | START WITH } integer
| { MAXVALUE integer | NOMAXVALUE }
| { MINVALUE integer | NOMINVALUE }
| { CYCLE | NOCYCLE }
| { CACHE integer | NOCACHE }
| { ORDER | NOORDER }
]... ;
Let's look at this example for using the Oracle CREATE SEQUENCE:
SQL> create sequence pubs1;
Sequence created.
SQL> select pubs1.nextval from dual;
NEXTVAL
----------
1
SQL> select pubs1.nextval from dual;
NEXTVAL
----------
2
In the example above, we used CREATE SEQUENCE to create a sequence called pubs1. We use the NEXTVAL function to retrieve the next value. You can get the current value by calling the CURRVAL function. You must call the NEXTVAL function before calling the CURRVAL function, or you will get an error. There is no current value to the sequence until the next value has been called at least once.
When you use Oracle CREATE SEQUENCE to create the sequence, you have a lot of flexibility as to how the sequence generates the next number.
SQL> create sequence pubs2
2 start with 8
3 increment by 2
4 maxvalue 10000
5 cycle
6 cache 5;
Sequence created.
SQL> select pubs2.nextval from dual;
NEXTVAL
----------
8
In this example, using Oracle CREATE SEQUENCE, the sequence pubs2 will start at number eight and increment by twos to 10000 and then start over. Let’s cover each of their parameters:
INCREMENT BY
Specify the interval between sequence numbers. This integer value can be any positive or negative integer, but it cannot be 0. This value can have 28 or fewer digits. The absolute of this value must be less than the difference of MAXVALUE and MINVALUE. If this value is negative, then the sequence descends. If the value is positive, then the sequence ascends. If you omit this clause, then the interval defaults to 1.
START WITH
Specify the first sequence number to be generated. Use this clause to start an ascending sequence at a value greater than its minimum or to start a descending sequence at a value less than its maximum. For ascending sequences, the default value is the minimum value of the sequence. For descending sequences, the default value is the maximum value of the sequence. This integer value can have 28 or fewer digits.
Note:
This value is not necessarily the value to which an ascending cycling sequence cycles after reaching its maximum or minimum value.
MAXVALUE
Specify the maximum value the sequence can generate. This integer value can have 28 or fewer digits. MAXVALUE must be equal to or greater than START WITH and must be greater than MINVALUE.
NOMAXVALUE
Specify NOMAXVALUE to indicate a maximum value of 1027 for an ascending sequence or -1 for a descending sequence. This is the default.
MINVALUE
Specify the minimum value of the sequence. This integer value can have 28 or fewer digits. MINVALUE must be less than or equal to START WITH and must be less than MAXVALUE.
NOMINVALUE
Specify NOMINVALUE to indicate a minimum value of 1 for an ascending sequence or -1026 for a descending sequence. This is the default.
CYCLE
Specify CYCLE to indicate that the sequence continues to generate values after reaching either its maximum or minimum value. After an ascending sequence reaches its maximum value, it generates its minimum value. After a descending sequence reaches its minimum, it generates its maximum value.
NOCYCLE
Specify NOCYCLE to indicate that the sequence cannot generate more values after reaching its maximum or minimum value. This is the default.
CACHE
Specify how many values of the sequence the database preallocates and keeps in memory for faster access. This integer value can have 28 or fewer digits. The minimum value for this parameter is 2. For sequences that cycle, this value must be less than the number of values in the cycle. You cannot cache more values than will fit in a given cycle of sequence numbers. Therefore, the maximum value allowed for CACHE must be less than the value determined by the following formula:
(CEIL (MAXVALUE - MINVALUE)) / ABS (INCREMENT)
If a system failure occurs, all cached sequence values that have not been used in committed DML statements are lost. The potential number of lost values is equal to the value of the CACHE parameter.
Note:
Oracle recommends using the CACHE setting to enhance performance if you are using sequences in a Real Application Clusters environment.
NOCACHE
Specify NOCACHE to indicate that values of the sequence are not preallocated. If you omit both CACHE and NOCACHE, the database caches 20 sequence numbers by default.
ORDER
Specify ORDER to guarantee that sequence numbers are generated in order of request. This clause is useful if you are using the sequence numbers as timestamps. Guaranteeing order is usually not important for sequences used to generate primary keys.
ORDER is necessary only to guarantee ordered generation if you are using Oracle Database with Real Application Clusters. If you are using exclusive mode, sequence numbers are always generated in order.
NOORDER
Specify NOORDER if you do not want to guarantee sequence numbers are generated in order of request. This is the default.
Example:
Creating a Sequence:
The following statement creates the sequence customers_seq in the sample schema oe. This sequence could be used to provide customer ID numbers when rows are added to the customers table.
CREATE SEQUENCE customers_seq START WITH 1000 INCREMENT BY 1 NOCACHE NOCYCLE;
The first reference to customers_seq.nextval returns 1000. The second returns 1001. Each subsequent reference will return a value 1 greater than the previous reference.