 |
|
Oracle Tips by Burleson |
Administration of Sequences
Sequences are special database objects that
provide numbers in sequence for input to a table. They are useful
for providing generated primary key values and for input of number
type columns such as purchase order, employee number, sample number,
and sales order number, where the input must be unique and in some
form of numerical sequence.
Creation of Sequences
Sequences are created by use of the CREATE
SEQUENCE command. The command’s format follows:
See Code Depot
where:
sequence_name. The name you want the
sequence to have. This may include the user name if created from an
account with DBA privilege.
n. An integer, positive or negative.
INCREMENT BY. Tells the system how to
increment the sequence. If it is positive, the values are ascending;
if it is negative, the values are descending.
START WITH. Tells the system which
integer to start with.
MINVALUE. Tells the system how low the
sequence can go. For ascending sequences, it defaults to 1; for
descending sequences, the default value is 10e27-1.
MAXVALUE. Tells the system the highest
value that will be allowed. For descending sequences, the default is
1; for ascending sequences, the default is 10e27-1.
CYCLE. Causes the sequence to
automatically recycle to minvalue when maxvalue is reached for
ascending sequences; for descending sequences, it causes a recycle
from minvalue back to maxvalue.
CACHE. Caches the specified number of
sequence values into the buffers in the SGA. This speeds access, but
all cached numbers are lost when the database is shut down. The
default value is 20; maximum value is maxvalue-minvalue.
ORDER. Forces sequence numbers to be
output in order of request. In cases where they are used for
timestamping, this may be required. In most cases, the sequence
numbers will be in order anyway, so ORDER will not be required.
ORDER is necessary only to guarantee ordered generation if you are
using Oracle with the Oracle Real Application Clusters option in
parallel mode. If you are using exclusive mode, sequence numbers are
always generated in order.
www.oracle-script.com |
