In this oracle tutorial, we will learn how to construct a new sequence in Oracle using the Oracle CREATE SEQUENCE statement.
An Oracle object called a sequence is used to create a numerical sequence. When you need to generate a special number to use as a primary key, this can be helpful.
Here we will discuss
- How to create a Sequence in Oracle 21c
- Oracle create sequence if not exists
- Oracle create sequence to increment by 1
- Oracle create sequence with start value from select
- Oracle create sequence for primary key
- Oracle create sequence order
- Oracle create sequence with max value
- Oracle create sequence replace
- Oracle create sequence on column
- Oracle create sequence and trigger
- Oracle create alter sequence
- Oracle create sequence in stored procedure
- Oracle create sequence default values
How to create a sequence in oracle 21c
- Only users with the CREATE SEQUENCE privilege can create Sequences in their own schema. Granting the Build ANY SEQUENCE privilege will enable a user to create a Sequence in any schema.
- No matter whether the transaction commits or rolls back, the sequence is incremented when a sequence number is generated.
Oracle create sequence syntax
Till now, we have understood what sequencing is in a database and we also discussed the purpose of sequencing. Next, we will take a step further and understand how to create a sequence in oracle 21c.
For this, we need to understand the syntax to create a sequence in oracle 21c which is given below.
CREATE SEQUENCE schema_name.seq_name
[INCREMENT BY interval]
[START WITH initial_number]
[MAXVALUE max_value | NOMAXVALUE]
[MINVALUE min_value | NOMINVALUE]
[CYCLE | NOCYCLE]
[CACHE cache_size | NOCACHE]
[ORDER | NOORDER];
In the above syntax
- CREATE SEQUENCE: After the CREATE SEQUENCE keywords, add the sequence’s name.
- INCREMENT: After the INCREMENT BY keyword, it specifies the gap between sequence numbers. Less than 28 digits can comprise the interval. Furthermore, it has to be lower than MAXVALUE – MINVALUE. The sequence will begin producing numbers at 1 and increase by 1.
- start: This parameter defines the initial number in the sequence. The seq series begins with the given value of 0 because the START WITH clause is set to 0.
- MAXVALUE: Indicate the sequence’s greatest value.
- If you want to specify a maximum value of 10^27 for an ascending series or -1 for a descending sequence, you can specify NOMAXVALUE.
- MINVALUE: MINVALUE should be less than or similar to START WITH and must be lower than MAXVALUE.
- NOMINVALUE: To provide a minimum value of 1 for an ascending series or -10^26 for a descending sequence, use the NOMINVALUE keyword.
- CYCLE: If we define CYCLE, the sequence will keep producing values even after it reaches either its minimum or maximum value.
- NOCYCLE: If you want the sequence to end by producing the next number once it reaches its limit, use NOCYCLE.
- CACHE: You can specify how many sequence values Oracle will preallocate and maintain in memory for quicker access. The cache’s smallest size is two.
- Order: To make sure Oracle generates sequence numbers in the order of requests, use ORDER.
Note:
You need to have the CREATE SEQUENCE system privilege in order to create a sequence in your own schema.
Read: Oracle Database vs MySQL Database
Oracle Create Sequence Example
After discussing the syntax, Now we will discuss the example of how to create a sequence in oracle.
CREATE SEQUENCE id_seq
INCREMENT BY 10
START WITH 10
MINVALUE 10
MAXVALUE 100
CYCLE
CACHE 2;

In the above example, we created a sequence called, starting from 5, incrementing by 20, minimum value 2, maximum value 1000. The CYCLE parameter causes the sequence to return 5 once it reaches 1000.
We will utilize the NEXTVAL to obtain the next value in the sequence.
SELECT
new_seq.NEXTVAL
FROM
dual;

This is how to create a sequence in oracle 21c.
Read: How to create a database in Oracle 19c
Oracle create sequence to increment by 1
To construct a series for the customer number column, use the simple Create Sequence command. Because there is no Order clause provided, the series will begin generating numbers at 1 and increase by 1, but not in any particular order.
Example:
CREATE SEQUENCE customer_seq
INCREMENT BY 1;

We will utilize the NEXTVAL to obtain the next value in the sequence.
SELECT
customer_seq.NEXTVAL
FROM
dual;

In this example, we have understood how to create a sequence to increment by 1 in oracle.
Read: How to connect to the Oracle database
Oracle create sequence with a max value
- Now we will discuss how to create a sequence in oracle with having max value.
- In this example, we will create a sequence with the MAXVALUE option. After reaching number six, this sequence will stop generating the number.
- The first number specified after the START WITH keywords must be equal to or more than the max value.
Oracle Create Sequence with having max value Example
Now we will discuss the example of how to create a sequence in oracle with max value.
CREATE SEQUENCE employee_new_seq start with 1
INCREMENT BY 1 maxvalue 6;

We will use the NEXTVAL to fetch the next value in the sequence.
SELECT
employee_new_seq.NEXTVAL
FROM
dual;
In the above example, we have set the starting point of the sequence and the MAXVALUE to set the endpoint of the sequence. In this example, the starting value is 1 and the max value is 6.
If you will set the starting point=7 then it will raise an error because we have already set the maximum value as 6.


This kind of error raises if you set the starting value to more than the max value.
Read: How to create a user in Oracle
Oracle create sequence order
- In this section, we will understand how to create a sequence to produce numbers in order.
- To make sure Oracle generates sequence numbers in the order of requests, by using the ORDER option.
For this, we need to understand the syntax to create a sequence to generate numbers in order in oracle 21c which is given below.
CREATE SEQUENCE seq_name
[INCREMENT BY order]
[START WITH initial_number]
[MAXVALUE max_value | NOMAXVALUE]
Oracle Create Sequence Example
After discussing the syntax, Now we will discuss the example of how to create a sequence in oracle.
CREATE SEQUENCE student_seq
INCREMENT BY 1 order
START WITH 90
MAXVALUE 100;
In this example, we have created a sequence ‘student_seq’ and assigned the starting value of 90 with a maximum value that is 100. Now we want to display the numbers between 90-100 in sequence order increment by 1 value.
SELECT
student_seq.NEXTVAL
FROM
dual
CONNECT BY level <=9;
In the above code, we used the nextval for generating the next value and then use This SELECT statement using the student_seq.NEXTVAL value repeatedly increments by 1.
Note:
The CONNECT BY clause establishes the hierarchy between the parent rows and the child rows.

This is how to create a sequence to generate numbers in order.
Read: How to get database name in Oracle
Oracle create alter sequence
- Here we will discuss how to modify a sequence object’s attributes and behavior using the Oracle ALTER SEQUENCE statement.
- We can easily change an existing sequence’s increment, minimum and maximum values, and cached number with the ALTER SEQUENCE command.
Oracle alter sequence syntax
Till now, we have understood what altering is in a database and we also discussed the purpose of altering. Next, we will take a step further and understand how to alter a sequence in oracle 21c.
For this, we need to understand the syntax to alter a sequence in oracle 21c which is given below.
ALTER SEQUENCE sequence_name
[INCREMENT BY interval]
[START WITH initial_number]
[MAXVALUE max_value | NOMAXVALUE]
[MINVALUE min_value | NOMINVALUE]
[CYCLE | NOCYCLE]
[CACHE cache_size | NOCACHE]
[ORDER | NOORDER];
In this example, we will modify the increment value of a given sequence for example when we created the sequence we mention the increment value 2. Now we want to change the value from 2 to 3.
For this first, we will create the sequence and set the starting value max value as well as the increment option.
CREATE SEQUENCE number_sequence
INCREMENT BY 2
START WITH 6
MAXVALUE 20;
SELECT
number_sequence.NEXTVAL
FROM
dual
CONNECT BY level <=7;

Now we will change the increment value by 2 to 3 by using the alter statement
ALTER SEQUENCE number_sequence
INCREMENT BY 3;

Now to display the values after modifying, we will use the below query
SELECT
number_sequence.NEXTVAL
FROM
dual
CONNECT BY level <4;

As you can see in the Screenshot the values have been updated and the values are incremented by 3.
Read: How to check database status in Oracle
Oracle create sequence on column
- In this section, we will discuss how to create a sequence on a column in the Oracle 21c
- The CREATE TABLE statement allows you to specify the sequence’s NEXTVAL configuration and assign a sequence to a table column.
Here is the table for our example execution and this customers table is located in the pdb database.

Next, we will create a sequence for the customer_id column of the customers table
CREATE SEQUENCE customer_id_seq;

After creating the sequence we will insert the values into the customer table
INSERT INTO customers VALUES(customer_id_seq.NEXTVAL, 'Sequence created in Oracle');
INSERT INTO customers VALUES(customer_id_seq.NEXTVAL, 'Examine Sequence Values');
Now to fetch the data into the column, we can easily use the below query
SELECT
customer_id, title
FROM
customers;

In this example, we have understood how to create a sequence on a column in the Oracle 21c.
Read: Install sample schemas 21c in Oracle
Oracle create sequence for primary key
- Now let us discuss how to create a sequence for the primary key in oracle.
- The table we’ll be utilizing must first be CREATED before we can create a unique primary key for it.
Here is the table for our example execution and this employees table is located in the pdb database.

In the above employee table, we have assigned the emp_id as a primary key and now I want to create a sequence in the primary key. For this first, we will create a sequence.
Next, we will create a sequence for the emp_id column of the employees table
CREATE SEQUENCE empi_id_seq;
In this step, we’ll design the SEQUENCE that will be used to produce the unique, After creating the sequence we will insert the values into the employee table
INSERT INTO employees VALUES(empi_id_seq.NEXTVAL, 'U.S.A');
INSERT INTO employees VALUES(empi_id_seq.NEXTVAL, 'China');
Now we will display the primary key column values
SELECT
emp_id, country
FROM
employees;

Oracle create sequence and trigger
In this instance, we want to run our TRIGGER first, making sure that it increments our SEQUENCE and passes the new value onto our primary key column before we insert data into the employees database.
Oracle create sequence replace
If a procedure with the same name already exists in the database schema, Oracle CREATE OR REPLACE’s OR REPLACE clause enables the compiler to replace it.
In oracle, we can also use Create or replace but are restricted to some objects like sequences. Therefore we cannot replace the existing sequence in oracle.

As you can see in the Screenshot the error has occurred and the possible reason behind this error is that replace does not work in sequence objects while working with oracle 21c.
Read: How to backup a table in Oracle
Oracle create sequence default values
- In this section, we will discuss how to create a sequence with default values in oracle.
- To create a sequence, the CREATE SEQUENCE statement supports a number of clauses. By default, a new sequence of integers does not have a minimum or maximum limit.
- The default value for the INCREMENT BY feature in a database sequence is 1, which results in an ascending sequence that increases the integer values by 1. Set INCREMENT BY to a negative integer to produce a descending order. It is not possible to set INCREMENT BY to 0.
- The seq series begins with the given value of 0 because the START WITH clause is set to 0.
Example of Oracle create sequence default values
CREATE SEQUENCE new_sequence
START WITH 0
MINVALUE 0
INCREMENT BY 1
ORDER;

The new_sequence sequence begins with the provided value of 0 because the START WITH clause is set to 0. The minimum value in the new_sequence sequence is set to 0 in the MINVALUE clause.
An ascending sequence’s default minimum value is 1. The value of the INCREMENT BY clause is 1. The ORDER clause ensures that the request-based order will be maintained while generating the sequence integers.
Read: How to Get List all Tables in Oracle Database
Oracle create sequence if not exists
- Now let us understand how to create a sequence in oracle if does not exists in oracle.
- To discover all sequences available to the schema user, use the select sequence name from all sequences.
select * from User_Sequences
where sequence_name='number_se';

Now we will take an existing sequence.

Oracle create sequence with start value from select
- In this section, we will discuss how to create a sequence with a start value from select.
- Only users with the CREATE SEQUENCE privilege can create Sequences in their own schema. Granting the Build ANY SEQUENCE privilege will enable a user to create a Sequence in any schema.
declare
ID number;
begin
select MAX(EMP_ID) + 1 into ID from EMPLOYEES;
If ID > 0 then
begin
execute immediate 'DROP SEQUENCE new_sequence';
exception when others then
null;
end;
execute immediate 'CREATE SEQUENCE new_sequence INCREMENT BY 1 START WITH ' || ID || ' NOCYCLE CACHE 20 NOORDER';
end if;
end;

In the above code first, we created the employees table and then we add the values into the emp_id column select MAX(EMP_ID) + 1 into ID from EMPLOYEES;
Next, we will fetch the values by using the below query
SELECT
new_sequence.NEXTVAL
FROM
dual;

Read: How to add a primary key in Oracle
Oracle create sequence in stored procedure
- Here we will discuss how to create a sequence in a stored procedure in oracle 21c.
- An important layer of protection is added by a stored procedure between the user interface and the database. Because end users can add or modify data but not develop procedures, it enables security through data access controls.
Here is the table for our example execution and this student table is located in the pdb database.

Now we will discuss the example of how to create a sequence in oracle.
CREATE SEQUENCE new_stu_seq
START WITH 1
INCREMENT BY 1;

CREATE OR REPLACE PROCEDURE stu_Insert AS
BEGIN
INSERT INTO student (stu_id)
VALUES (new_stu_seq.nextval);
COMMIT;
END stu_Insert;

You may also like to read the following Oracle tutorials.
- Connect to Oracle Database using Python
- How to Fetch Data from Oracle Database in Python
- Connect Excel to Oracle Database
In this oracle tutorial, we have learned how to create a new sequence in Oracle using the Oracle CREATE SEQUENCE statement. And also we have covered the following given topics
- How to create a Sequence in Oracle 21c
- Oracle create sequence if not exists
- Oracle create sequence to increment by 1
- Oracle create sequence with start value from select
- Oracle create sequence for primary key
- Oracle create sequence order
- Oracle create sequence with max value
- Oracle create sequence replace
- Oracle create sequence on column
- Oracle create sequence and trigger
- Oracle create alter sequence
- Oracle create sequence in stored procedure
- Oracle create sequence default values
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.