SYNTAX:-
CREATE SEQUENCE SEQUENCE_NAME
START WITH
INCREMENT BY
NOCACHE
NOCYCLE;
EXAMPLE:-
Customers_Seq is Sequnce Name, It will start with 1000 and increment by 1 each time.
CREATE SEQUENCE customers_seq
START WITH 1000
INCREMENT BY 1
NOCACHE
NOCYCLE;
NOCACHE / CACHE:-
The cache option specifies how many sequence values will be stored in memory for faster access.
The downside of creating a sequence with cache is that if a system failure occurs, all the cache sequence values that have not been used will be lost.
This results a gap in t he assigned sequence values. When the system comes back, Oracle will cache new numbers from where it left off in the sequence, ignoring the so called "last sequence values".
TIP: To recover the lost sequence values, you can always execute an ALTER SEQUENCE command to reset the counter to the correct value
NoCache means, that none of the values are stored in memory. This option may sacrifice some performance. However, you should not encounter a gap in the assigned sequence values.
NOCYCLE
Use NOCYCLE if you want the sequence to stop generating the next value when it reaches its limit.
