ORACLE SEQUENCE Introduction

In Oracle in the sequence serial number is called, every time, it will take the initiative to increase itself, by the sequence number needed to sort the general use.
1. Create Sequence
You must first create sequence or create any sequence permissions.
CREATE SEQUENCE emp_sequence
INCREMENT by 1--add a few each time
Start with 1-Start counting from 1
Nomaxvalue--Do not set the maximum value
Nocycle--keep accumulating, not looping
CACHE 10;

Once you have defined emp_sequence, you will be able to use Currval. Nextval
Currval= returns the current value of the sequence
Nextval= adds the value of the sequence. Then return the sequence value
Example:
Emp_sequence. Currval
Emp_sequence. Nextval

Where to use the sequence:
-Do not include sub-query, snapshot, view's SELECT statement
-INSERT statement in subqueries
-In the values of the Nsert statement
-In the set of UPDATE

Can look for example the following examples:
INSERT into EMP VALUES
(Empseq.nextval, ' LEWIS ', ' clerk ', 7902, Sysdate, N, NULL, 20);

SELECT Empseq.currval from DUAL;

However, it is important to note that:
-The first time Nextval returns the initial value. The nextval will then voluntarily add your defined increment by value and return the added value. Currval always returns the value of the current sequence, but if Currval is used after the first nextval initialization, an error occurs. Once Nextval will add a value of sequence once, so suppose you use multiple nextval in the same statement. The value is not the same.

Clear?

-Assume that the cache value is specified. Oracle has the ability to pre-sequence some of the memory inside, so it can be accessed faster. After the cache was taken, Oracle took the initiative to take a group to the cache. Using the cache may jump the number. For example, if the database suddenly fails down (shutdown abort), the sequence in the cache will be lost. So it is possible to use NoCache to prevent such a situation when create sequence.

2. Alter Sequence
You or the owner of the sequence. Or have alter any SEQUENCE permission ability to modify SEQUENCE. Ability to alter all sequence parameters except start to. False assumption to change the start value. Must drop sequence again re-create.
Examples of Alter sequence
ALTER SEQUENCE emp_sequence
INCREMENT by 10
MAXVALUE 10000
CYCLE-Start from the beginning in 10000
NOCACHE;


Shadow ring sequence Initialization parameters:
Sequence_cache_entries = Setting the same time can be the SEQUENCE number of the CACHE.

Can be very easy to drop Sequence
DROP SEQUENCE Order_seq;

ORACLE SEQUENCE Introduction