About Sequences and their attributes in Oracle10g
A sequence is a database object used to generate unique integers. It is generally used to automatically generate primary key values.
To create sequence, CREATE SEQUENCE privilege must be given in users own schema or in the case of creation in another users schema, CREATE ANY SEQUENCE privilege must be given.
The command for creation of a sequence is
[schema .] sequence
Here schema specifies the schema to contain the sequence, default is current and own schema, sequence is the name of the sequence, INCREMENT BY specifies the numerical interval between sequence numbers, START WITH specifies the first sequence number ,MAXVALUE specifies the upper limit value of the number to be generated , NOMAXVALUE specifies upper limit as default of 1027 for an ascending sequence or -1 for a descending sequence, MINVALUE specifies the lowest value of the sequence, NOMINVALUE indicates a lowest value of 1 for an ascending sequence or -1026 for a descending sequence, CYCLE specifies that sequence continues to generate values after reaching maximum or minimum value by starting afresh, specify NOCYCLE to indicate sequence stops after generating maximum or minimum value, CACHE to specify the number of values of the sequence that Oracle pre allocates and keeps in memory for faster access, NOCACHE to indicate the values are not pre allocated, ORDER to guarantee that values are ordered as requested (necessary only with Oracle with Real Application Clusters) and NOORDER if no requirement to guarantee the order of sequence.
The first reference made to EMP_SEQ.NEXTVAL returns 1000, the next 1001 and henceforth.
can be altered. To alter sequences in other schemas, user must have the
ALTER ANY SEQUENCE system privilege. Using ALTER command, any of the parameters
already defined in a sequence can be altered except the sequence starting
can also be dropped in the users schema. To drop any sequence in
another schema the user must have DROP ANY SEQUENCE privilege.
When a sequence
is created, its values can be accessed in SQL statements with the CURRVAL
pseudo column which returns the current value of the sequence or the NEXTVAL
pseudo column which returns the next value after incrementing.
reference to EMP_SEQ.NEXTVAL returns the value 1. Subsequent references
cause the next value in sequence to be generated. Independent of transactions
being committed or rolled back, other users referencing EMP_SEQ.NEXTVAL
obtain unique values. If there is more than one user accessing the same
sequence, the sequence number may have gaps, because sequence number is
being generated by the other users. To
use or refer the current value of sequence, EMP_SEQ.CURRVAL is used. This
can be used only if EMP_SEQ.NEXTVAL has been referenced in the current
number can be referenced in SELECT of a query or sub query.
CURRVAL and NEXTVAL can be used only in VALUES clauses of INSERT statements, the SELECT list of a SELECT statement and the SET clause of an update statement.
Without sequences, sequential values can be only produced by program logic. Obtaining sequential values programmatically may cause locks in transactions and multiple users may need to wait.
can thus help eliminate serialization and improve concurrency of a transaction.
| About Sequences and their attributes in Oracle10g | Accessing Remote data with Database Links in Oracle10g | A Guide to Iterative Processing with Loops in PL/SQL | A note on Dynamic SQL and its implementation in PL/SQL Application | Autonomous Transactions in Oracle How to create and use if efficiently | Backup and Recovery Best Practices in Oracle10g | Compiling Procedures, Functions and Packages during Application Development in Oracle10g | Introducing Publish-Subscribe Model in Oracle10g | Exploring Debugging Procedures in Oracle10g | External Procedures and their uses in Oracle10g | Guidelines for Locking Data in Oracle10g | How to customize an oracle10g Database using Database triggers | Specifying Constraints while creating table in Oracle10g to enhance Data Integrity |
Copyright - © 2004 - 2018 - All Rights Reserved.