Oracle Sequence Management

Create Sequence

Most simplest way of creating sequence.

create sequence xyz;

Since we have not specified the starting number, by default it will start with 1.

Fetching the sequence

Get the current sequence value.

select xyz.currval from dual;

Since we have not yet retrieved the sequence, trying to fetch current value will fail.

ORA-08002: sequence XYZ.CURRVAL is not yet defined in this session
08002. 00000 -  "sequence %s.CURRVAL is not yet defined in this session"
*Cause:    sequence CURRVAL has been selected before sequence NEXTVAL
*Action:   select NEXTVAL from the sequence before selecting CURRVAL

Let's now issue nextval.

select xyz.nextval from dual;

Result:

1

Now we can get the current sequence value.

select xyz.currval from dual;

Result:

1

Next sequence value

Get the next sequence.

select xyz.nextval from dual;

Result:

2

As you can see, by default, sequence gets incremented by 1. Let's drop the sequence and recreate it with more details.

Drop the sequence

drop sequence xyz;

Result:

Sequence XYZ dropped.

Create Sequence with more details

We can specify a start, increment and the max/min value.

create sequence xyz start with 3 increment by 2 maxvalue 10 minvalue 3;

Next value:

select xyz.nextval from dual;

Result:

3

Next value will be 5, 7 and 9. After the sequence returns 9, we will try to fetch the next value.

select xyz.nextval from dual;

ORA-08004: sequence XYZ.NEXTVAL exceeds MAXVALUE and cannot be instantiated
08004. 00000 -  "sequence %s.NEXTVAL %s %sVALUE and cannot be instantiated"
*Cause:    instantiating NEXTVAL would violate one of MAX/MINVALUE
*Action:   alter the sequence so that a new value can be requested

It's bound to fail as the next value will be 11 which > than the maximum allowed.

Cycle

In case we want the sequence to start a new cycle after reaching either its maximum or minimum value, we will have to create the sequence with keyword cycle. After reaching its maximum value, the sequence will generate the minimum value as the next value.

create sequence xyz start with 3 increment by 2 maxvalue 10 minvalue 3 CYCLE;

Result:

Error starting at line : 3 in command -
create sequence xyz start with 3 increment by 2 maxvalue 10 minvalue 3 CYCLE
Error report -
ORA-04013: number to CACHE must be less than one cycle
04013. 00000 -  "number to CACHE must be less than one cycle"
*Cause:    number to CACHE given is larger than values in a cycle
*Action:   enlarge the cycle, or cache fewer values

It fails as database pre-allocates and keeps the sequences in memory for faster access and by default it caches next 20 sequences. Since our cycle consists of 10 numbers, the sequence creation fails Above sequence starts 3, skipping next 20 values, we will hit value 43, so creating sequence with 43 as maximum will work fine.

create sequence xyz start with 3 increment by 2 maxvalue 43 minvalue 3 cycle;

Let's try with our previous sequence creation, see if it works with a cache of 1.

create sequence xyz start with 3 increment by 2 maxvalue 10 minvalue 3 CYCLE cache 1;

Result:

Error report -
ORA-04010: the number of values to CACHE must be greater than 1
04010. 00000 -  "the number of values to CACHE must be greater than 1"
*Cause:    the value in the CACHE clause was one
*Action:   specify NOCACHE, or a value larger than one

Fails again as the minimum cache allowed is 2.

create sequence xyz start with 3 increment by 2 maxvalue 10 minvalue 3 CYCLE cache 2;

Now with cycle enabled, sequences generated will be 3, 5, 7, 9| 3, 5, .... After reaching 9, it restarts sequence generation with the minimum value.

How to reset sequence's nextval?

Suppose you are using hibernate as the ORM layer, by default hibernate relies on HIBERNATE_SEQUENCE to generate the sequences. There may be situations where you may want to explicitly insert records using raw SQL. One may want to provide the sequence numbers locally, for example, using a long variable instead of fetching the next value from hibernate sequence. Once all the rows are inserted, we must reset the nextval of HIBERNATE_SEQUENCE so that we don't end up with duplicate row issues. In the below method, we show how to reset the next value. Let's create a sequence with an increment of 1.

create sequence xyz start with 3 increment by 1;

Get next value:

select xyz.nextval from dual;

Result:

3

Let's assume we want to restart sequence generation from 7 onwards. Example, 7, 8, etc. To achieve we will first alter the sequence to reset the increment.

alter sequence xyz increment by 4;

Now get the next value.

select xyz.nextval from dual;

Result:

7

Reset the increment to 1.

alter sequence xyz increment by 1;

Get the next value.

select xyz.nextval from dual;

8