A sequence is a database object that generates unique numbers, mostly used for primary key values.
One can select the NEXTVAL and CURRVAL from a sequence. Selecting the NEXTVAL will automatically increment the sequence.
Create a sequence
CREATE SEQUENCE INV.MTL_MATERIAL_TRANSACTIONS_S START WITH 22611295 MAXVALUE 4294967295 MINVALUE 1 NOCYCLE CACHE 1000 NOORDER;
Selecting from the sequence:
SQL> select MTL_MATERIAL_TRANSACTIONS_S.nextval from dual; NEXTVAL ---------- 24177354 SQL> select MTL_MATERIAL_TRANSACTIONS_S.nextval from dual; NEXTVAL ---------- 24177355
Sequence Related Queries
SELECT * FROM DBA_OBJECTS WHERE OBJECT_TYPE = 'SEQUENCE' AND OBJECT_NAME = 'MTL_MATERIAL_TRANSACTIONS_S'; SELECT * FROM DBA_SEQUENCES WHERE SEQUENCE_NAME = 'MTL_MATERIAL_TRANSACTIONS_S';
Key Fields in DBA_SEQUENCES
SEQUENCE_OWNER Name of the owner of the sequence SEQUENCE_NAME SEQUENCE name MIN_VALUE Minimum value of the sequence MAX_VALUE Maximum value of the sequence INCREMENT_BY Value by which sequence is incremented CYCLE_FLAG Does sequence wrap around on reaching limit? ORDER_FLAG Are sequence numbers generated in order? CACHE_SIZE Number of sequence numbers to cache LAST_NUMBER Last sequence number written to disk
LAST_NUMBER is influenced by the CACHE size. I saw that my LAST_NUMBER was 21 (my cache was 20). When I ran NEXTVAL I was getting a number like 4, then 5. I went up past 20 and my LAST_NUMBER jumped to 41 so everytime the cached numbers ran out another group of cached numbers would be reserved and the LAST_NUMBER field would change, but this has no relation to the CURRVAL.
Frequently Asked Questions About Sequences (Doc ID 108643.1)
Determining appropriate Sequence Cache value :http://srivenukadiyala.wordpress.com/2010/12/09/determining-appropriate-sequence-cache-value/