Sequence

• Can automatically generate unique numbers
• Is a sharable object
• Can be used to create a primary key value
• Replaces application code
• Speeds up the efficiency of accessing sequence values when cached in memeory
  

Syntax

CREATE SEQUENCE sequence

[INCREMENT BY n]

[START WITH n]

[{MAXVALUE n | NOMAXVALUE}]

[{MINVALUE n | NOMAXVALUE}]

[{CYCLE | NOCYCLE}]

[{CACHE n | NOCACHE}];

default increment value : 1
maxvalue : 10^27 (default)
minvalue : - 10^26 (default)
no cache : default
By default, the Oracle server caches 20 values

Ex)

CREATE SEQUENCE     dept_deptid_seq
                                  INCREMENT BY 10
                                  START WITH 120
                                  MAXVALUE 9999
                                  NOCACHE
                                  NOCYCLE;

** Do not use the CYCLE option if the sequence is used to generate primary key value, unless you have a reliable mechanism that purges old rows faster than sequence cycle.