Tag Archives: Drop sequence

Manage Sequence in Oracle

Managing Sequence 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

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: preallocates 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 INCREENT 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;

Advertisements