Common Oracle database objects-Sequence

Common Oracle database objects-Sequence
1. sequence: a database object provided by oacle for generating a series of unique numbers.
A) sequence features
I. The unique value is automatically provided.
Ii. shared object
Iii. Mainly used to provide the primary key value
Iv. Loading Sequence values into memory improves access efficiency
B) sequence definition
Create sequence sequence
[Increment by n]
[Start with n]
[{Maxvalue n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | COCACHE}];
Ii. Sequence Creation
A) You must have the create sequence or create any sequence permission to create a sequence.
B) sequence creation syntax
Create sequence sequence // CREATE a SEQUENCE name
[Increment by n] // the incremental sequence value is n. If n is a positive number, it increases. If n is a negative number, it decreases. The default value is 1.
[Start with n] // The START value. The default value of increment is minvalue and the value of increment is maxvalue.
[{MAXVALUE n | NOMAXVALUE}] // maximum value
[{MINVALUE n | NOMINVALUE}] // minimum value
[{CYCLE | NOCYCLE}] // cyclic/non-cyclic
[{CACHE n | NOCACHE}]; // The allocation is included in the memory.
C) Notes
I. NEXTVAL returns the next valid value in the sequence, which can be referenced by any user.
Ii. Store the current value of the sequence in CURRVAL
Iii. NEXTVAL should be specified before CURRVAL, and both should be valid at the same time
Iii. Sequence usage
A) Loading Sequence values into memory improves access efficiency
B) The sequence has cracks in the following circumstances:
I. rollback
Ii. system exceptions
Iii. Multiple tables use the same sequence at the same time
C) if you do not load the sequence value into the memory (NOCACHE), you can use the USER_SEQUENCES table to view the current valid values of the sequence.
4. Modify the sequence
A) modify the sequence increment, maximum value, minimum value, loop option, or whether to load the memory.
Example: alter sequence dept_deptid_seq
Increment by 20
Max value 999999
NOCACHE
NOCYCLE;
Sequence altered.
 
B) Considerations
I. It must be the sequence owner or have the ALTER permission on the sequence.
Ii. Only future sequence values will be changed
Iii. Changing the initial values of a sequence can only be achieved by deleting the sequence and recreating the sequence.
Iv. Use the drop sequence statement to delete a SEQUENCE
V. After deletion, the sequence cannot be referenced again



From song lixing's column