Oracle-SEQUENCE )-

In oracle, sequence is the so-called serial number. It will be automatically added each time it is obtained.
Where the serial number is sorted.

1. create sequence
First, you must have the create sequence or create any sequence permission,
Create sequence emp_sequence
Increment by 1 -- add several
Start with 1 -- count from 1
NOMAXVALUE -- do not set the maximum value
NOCYCLE -- always accumulate without repeating
CACHE 10; -- the pre-allocated CACHE size is 10

Once emp_sequence is defined, you can use CURRVAL, NEXTVAL
CURRVAL = returns the current sequence Value
NEXTVAL = increase the sequence value, and then return the sequence Value
For example:
Emp_sequence.CURRVAL
Emp_sequence.NEXTVAL

Where sequence can be used:
-SELECT statements that do not contain subqueries, snapshot, and VIEW
-The INSERT statement is in the subquery.
-In the value of the NSERT statement
-UPDATE in SET

See the following example:
Insert into emp VALUES
(Em1_q. nextval, 'Lewis ', 'cler', 7902, SYSDATE, 1200, NULL, 20 );

SELECT empseq. currval from dual;

Note that:
-The first NEXTVAL returns the initial value. The subsequent NEXTVAL automatically increases the value of your defined increment,
Then return the added value. CURRVAL always returns the value of the current sequence, but in the first NEXTVAL
You can use CURRVAL only after initialization. Otherwise, an error occurs. NEXTVAL increases the value of sequence once,
Therefore, if you use multiple NEXTVAL statements, the values are different. Understand?
-If the CACHE value is specified, oracle can place some sequence in the memory in advance, so that the access is fast.
Some.
After the cache is obtained, oracle automatically retrieves another group to the cache. The cache may be skipped, for example
The database suddenly fails to shut down abort, and the sequence in the cache will be lost.
To prevent this situation by using nocache when creating sequence.

2. Alter sequence
You are either the owner of the sequence, or you have the alter any sequence permission to modify the sequence. Yes
All sequence parameters except the start value of alter. To change the start value, you must drop sequence.
Then re-create. Example:
ALTER sequence emp_sequence
Increment by 10
Max value 10000
CYCLE -- start from scratch after 10000
NOCACHE;

Initialization parameters that affect sequence:
Sequence_CACHE_ENTRIES =
Set the number of sequence that can be cached at the same time.

It's easy to Drop sequence.
DROP sequence order_seq;