Check the Sequence currval & nextval value in Oracle
Sequence is the oracle database object used for generating the unique sequence number. It is may or may not be used as primary key.
Create Sequence syntax as
CREATE SEQUENCE emp_seq INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE CACHE 10; Note: Start with: starting value of the Sequence it is anything CACHE clause: pre-allocates a set of sequence numbers and keeps them in memory so that sequence numbers can be accessed faster. Note: In case of CACHE enabled, the database might skip sequence numbers. For example, when an instance abnormally shuts down sequence numbers that have been cached are lost.
Alter a sequence to change any of the parameters that define it while creating.
ALTER SEQUENCE emp_seq INCREMENT BY 10 MAXVALUE 10000 CYCLE CACHE 20
Use of Sequence with NEXTVAL or CURRVAL
CURRVAL: current sequence value of your session
NEXTVAL: generate and use a sequence number
Note: Each time you use nextval it always show the next value as defined by INCREMENT Clause in Sequence
--Check the current value of SEQUENCE select emp_seq.CURRVAL from dual; --Insert current value of Sequence INSERT INTO Employee (id, empid, name) VALUES (emp_seq.CURRVAL, 20321, 'RAM'); -- Generate next value from Sequence SELECT emp_seq.NEXTVAL FROM dual; -- Insert next value in Sequence INSERT INTO Employee (id, empid, name) VALUES (emp_seq.NEXTVAL, 10324, 'ANIL');
Check the Sequence Details
select * from DBA_SEQUENCES;
Making a Sequence Scalable
In Sequence Scalable number 6 digit is added in front of the Sequence number.
scalable sequence number = 6 digit scalable sequence offset number || normal sequence number
6 digit scalable sequence offset number = 3 digit instance offset number || 3 digit session offset number.
SYNTAX: CREATE | ALTER SEQUENCE sequence_name ... SCALE [EXTEND | NOEXTEND] | NOSCALE
In this you have two options:
In Extended it will choose the sequence number as 6 digit of instance offset and y digit of MAXVALUE digit.
If MAXVALUE defined in sequence creating time is 100 then 3 digit is choose as Y digit.
6 digit scalable sequence offset number || 001
6 digit scalable sequence offset number || 002
6 digit scalable sequence offset number || 003
6 digit scalable sequence offset number || 100
In No Extended, the number of scalable sequence digits cannot exceed the number of digits specified in the MAXVALUE clause.
Example, it MAXVALUE is 1000000 7 digit then Y digit has only 1 digit left because 6 digit is already taken by instance offset
6 digit scalable sequence offset number || 1
6 digit scalable sequence offset number || 2
6 digit scalable sequence offset number || 3
6 digit scalable sequence offset number || 9
When you tried to add 10 at last then it will generate the following error:
ORA-64603: NEXTVAL cannot be instantiated for SQ. Widen the sequence by 1 digits
or alter sequence with SCALE EXTEND.
DROP SEQUENCE emp_seq;