A sequence generator can be used to automatically generate sequence numbers for rows in tables. A sequence is a database object created by a user and can be shared by multiple users.
A typical usage for sequences is to create a primary key value, which must be unique for each row. The sequence is generated and incremented (or decremented) by an internal Oracle8 routine. This can be a time-saving object because it can reduce the amount of application code needed to write a sequence generating routine.
Sequence numbers are stored and generated independently of tables. Therefore, the same sequence can be used for multiple tables.
SQL> CREATE SEQUENCE dept_deptno
2 INCREMENT BY 1
3 START WITH 91
4 MAXVALUE 100
5 NOCACHE
6 NOCYCLE;
Sequence created.
The example above creates a sequence named DEPT_DEPTNO to be used for the DEPTNO column of the DEPT table. The sequence starts at 91, does not allow caching, and does not allow the sequence to cycle.
Once you have created your sequence, it is documented in the data dictionary. Since a sequence is a database object, you can identify it in the USER_OBJECTS data dictionary table.
SQL> SELECT sequence_name, min_value, max_value,
2 increment_by, last_number
3 FROM user_sequences;
The NEXTVAL pseudocolumn is used to extract successive sequence numbers from a specified sequence. You must qualify NEXTVAL with the sequence name. When you reference sequence.NEXTVAL, a new sequence number is generated and the current sequence number is placed in CURRVAL.
The CURRVAL pseudocolumn is used to refer to a sequence number that the current user has just generated. NEXTVAL must be used to generate a sequence number in the current user’s session before CURRVAL can be referenced. You must qualify CURRVAL with the sequence name. When sequence.CURRVAL is referenced, the last value returned to that user’s process is displayed
Insert a new department named “MARKETING” in San Diego.
SQL> INSERT INTO dept(deptno, dname, loc)
2 VALUES (dept_deptno.NEXTVAL,
3 'MARKETING', 'SAN DIEGO');
1 row created.
View the current value for the DEPT_DEPTNO sequence.
SQL> SELECT dept_deptno.CURRVAL
2 FROM dual;
Caching sequence values in memory allows faster access to those values.
Gaps in sequence values can occur when:
A rollback occurs
The system crashes
A sequence is used in another table
View the next available sequence, if it was created with NOCACHE, by querying the USER_SEQUENCES table.
Modify
Guidelines
You must own or you have the ALTER privilege for the sequence in order to modify it.
Only future sequence numbers are affected by the ALTER SEQUENCE statement.
The START WITH option cannot be changed using ALTER SEQUENCE. The sequence must be dropped and re-created in order to restart the sequence at a different number.
Some validation is performed. For example, a new MAXVALUE cannot be imposed that is less than the current sequence number.
SQL> ALTER SEQUENCE dept_deptno
2 INCREMENT BY 1
3 MAXVALUE 999999
4 NOCACHE
5 NOCYCLE;
Sequence altered.
Removing a Sequence
To remove a sequence from the data dictionary, use the DROP SEQUENCE statement. You must be the owner of the sequence or have the DROP ANY SEQUENCE privilege to remove it.
SQL> DROP SEQUENCE dept_deptno;
Sequence dropped.
