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 Sequence
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:
EXTENDED:
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

NOEXTENDED:
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.

Dropping Sequences

DROP SEQUENCE emp_seq;