{"id":2244,"date":"2019-06-21T02:34:45","date_gmt":"2019-06-21T09:34:45","guid":{"rendered":"https:\/\/oracletutorial.com\/?page_id=2244"},"modified":"2025-05-03T15:34:07","modified_gmt":"2025-05-03T22:34:07","slug":"oracle-create-sequence","status":"publish","type":"page","link":"https:\/\/www.oracletutorial.com\/oracle-sequence\/oracle-create-sequence\/","title":{"rendered":"Oracle CREATE SEQUENCE"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the Oracle CREATE SEQUENCE<\/code> statement to create a new sequence in Oracle.<\/p>\n\n\n\n

Introduction to Oracle CREATE SEQUENCE statement #<\/a><\/h2>\n\n\n\n

The CREATE SEQUENCE<\/code> statement allows you to create a new sequence in the database.<\/p>\n\n\n\n

Here is the basic syntax of the CREATE SEQUENCE<\/code> statement:<\/p>\n\n\n

CREATE<\/span> SEQUENCE<\/span> schema_name.sequence_name\n[INCREMENT<\/span> BY<\/span> interval<\/span>]\n[START<\/span> WITH<\/span> first_number]\n[MAXVALUE max_value | NOMAXVALUE<\/span>]\n[MINVALUE<\/span> min_value | NOMINVALUE<\/span>]\n[CYCLE<\/span> | NOCYCLE<\/span>]\n[CACHE<\/span> cache_size | NOCACHE]\n[ORDER<\/span> | NOORDER<\/span>];\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
CREATE SEQUENCE #<\/a><\/h3>\n\n\n\n
Specify the name of the sequence after the CREATE SEQUENCE<\/code> keywords. If you want to create a sequence in a specific schema, you can specify the schema name in along with the sequence name.<\/p>\n\n\n\n
INCREMENT BY #<\/a><\/h3>\n\n\n\n
Specify the interval<\/code> between sequence numbers after the INCREMENT BY<\/code> keyword.<\/p>\n\n\n\n
The interval<\/code> can have less than 28 digits. It also must be less than MAXVALUE - MINVALUE<\/code>.<\/p>\n\n\n\n
If the interval<\/code> is positive, the sequence is ascending e.g., 1,2,3,…<\/p>\n\n\n\n
If the interval<\/code> is negative, the sequence is descending e.g., -1, -2, -3 …<\/p>\n\n\n\n
The default value of the interval<\/code> is 1.<\/p>\n\n\n\n
START WITH #<\/a><\/h3>\n\n\n\n
Specify the first number in the sequence.<\/p>\n\n\n\n
The default value of the first number is the minimum value of the sequence for an ascending sequence and the maximum value of the sequence for a descending sequence.<\/p>\n\n\n\n
MAXVALUE #<\/a><\/h3>\n\n\n\n
Specify the maximum value of the sequence.<\/p>\n\n\n\n
The max_value<\/code> must be equal to or greater than first_number<\/code> specify after the START WITH<\/code> keywords.<\/p>\n\n\n\n
NOMAXVALUE #<\/a><\/h3>\n\n\n\n
Use NOMAXVALUE<\/code> to denote a maximum value of 10^27 for an ascending sequence or -1 for a descending sequence. Oracle uses this option as the default.<\/p>\n\n\n\n
MINVALUE #<\/a><\/h3>\n\n\n\n
Specify the minimum value of the sequence.<\/p>\n\n\n\n
The min_value<\/code> must be less than or equal to the first_number<\/code> and must be less than max_value<\/code>.<\/p>\n\n\n\n
NOMINVALUE #<\/a><\/h3>\n\n\n\n
Use NOMINVALUE<\/code> to indicate a minimum value of 1 for an ascending sequence or -10^26 for a descending sequence. This is the default.<\/p>\n\n\n\n
CYCLE #<\/a><\/h3>\n\n\n\n
Use CYCLE<\/code> to allow the sequence to generate value after it reaches the limit, min value for a descending sequence, and max value for an ascending sequence.<\/p>\n\n\n\n
When an ascending sequence reaches its maximum value, it generates the minimum value.<\/p>\n\n\n\n
On the other hand, when a descending sequence reaches its minimum value, it generates the maximum value.<\/p>\n\n\n\n
NOCYCLE #<\/a><\/h3>\n\n\n\n
Use NOCYCLE<\/code> if you want the sequence to stop generating the next value when it reaches its limit. This is the default.<\/p>\n\n\n\n
CACHE #<\/a><\/h3>\n\n\n\n
Specify the number of sequence values that Oracle will preallocate and keep in the memory for faster access.<\/p>\n\n\n\n
The minimum of the cache size is 2. The maximum value of the cache size is based on this formula:<\/p>\n\n\n
(CEIL (MAXVALUE - MINVALUE)) \/ ABS (INCREMENT)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In case of a system failure event, you will lose all cached sequence values that have not been used in committed SQL statements.<\/p>\n\n\n\n
ORDER #<\/a><\/h3>\n\n\n\n
Use ORDER<\/code> to ensure that Oracle will generate the sequence numbers in order of request.<\/p>\n\n\n\n
This option is useful if you are using Oracle Real Application Clusters. When you are using exclusive mode, then Oracle will always generate sequence numbers in order.<\/p>\n\n\n\n
NOORDER #<\/a><\/h3>\n\n\n\n
Use NOORDER<\/code> if you do not want to ensure Oracle generates sequence numbers in order of request. This option is the default.<\/p>\n\n\n\n
Oracle CREATE SEQUENCE statement examples #<\/a><\/h2>\n\n\n\n
Let’s take some examples of using sequences.<\/p>\n\n\n\n
Basic Oracle Sequence example #<\/a><\/h3>\n\n\n\n
The following statement creates an ascending sequence called id_seq<\/code>, starting from 10, incrementing by 10, minimum value 10, maximum value 100. The sequence returns 10 once it reaches 100 because of the CYCLE<\/code> option.<\/p>\n\n\n
CREATE<\/span> SEQUENCE<\/span> id_seq\n    INCREMENT<\/span> BY<\/span> 10<\/span>\n    START<\/span> WITH<\/span> 10<\/span>\n    MINVALUE<\/span> 10<\/span>\n    MAXVALUE 100<\/span>\n    CYCLE<\/span>\n    CACHE<\/span> 2<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To get the next value of the sequence, you use the NEXTVAL<\/code> pseudo-column:<\/p>\n\n\n
SELECT<\/span> \n    id_seq.NEXTVAL \nFROM<\/span> \n    dual;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n
    NEXTVAL\n----------<\/span>\n        10        <\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To get the current value of the sequence, you use the CURRVAL<\/code> pseudo-column:<\/p>\n\n\n
SELECT<\/span> \n    id_seq.CURRVAL \nFROM<\/span> \n    dual;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The current value is 10:<\/p>\n\n\n
    CURRVAL\n----------<\/span>\n        10\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
This SELECT<\/code> statement uses the id_seq.NEXTVAL<\/code> value repeatedly:<\/p>\n\n\n
SELECT<\/span> \n    id_seq.NEXTVAL \nFROM<\/span> \n    dual\nCONNECT<\/span> BY<\/span> level<\/span> <= 9<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n
    NEXTVAL\n----------<\/span>\n        20\n        30\n        40\n        50\n        60\n        70\n        80\n        90\n    100\n\n9 rows selected \n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Because we set the CYCLE<\/code> option for the id_seq<\/code> sequence, the next value of the id_seq<\/code> will be 10:<\/p>\n\n\n
SELECT<\/span> id_seq.NEXTVAL FROM<\/span> dual;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n
    NEXTVAL\n----------<\/span>\n        10\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Using a sequence in a table column example #<\/a><\/h3>\n\n\n\n
Prior to Oracle 12c, you could associate a sequence indirectly with a table column only at the insert time. See the following example.<\/p>\n\n\n\n
First, create a new table<\/a> called tasks<\/code>:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> tasks(\n    id<\/span> NUMBER<\/span> PRIMARY KEY<\/span>,\n    title VARCHAR2<\/span>(255<\/span>) NOT<\/span> NULL<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, create a sequence for the id<\/code> column of the tasks<\/code> table:<\/p>\n\n\n
CREATE<\/span> SEQUENCE<\/span> task_id_seq;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
INSERT<\/span> INTO<\/span> tasks(id<\/span>, title)\nVALUES<\/span>(task_id_seq.NEXTVAL, 'Create Sequence in Oracle'<\/span>);\n\nINSERT<\/span> INTO<\/span> tasks(id<\/span>, title)\nVALUES<\/span>(task_id_seq.NEXTVAL, 'Examine Sequence Values'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SELECT<\/span>  \n    id<\/span>, title\nFROM<\/span>\n    tasks;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"Oracle<\/figure>\n\n\n\n
In this example, the tasks<\/code> table has no direct association with the task_id_seq<\/code> sequence.<\/p>\n\n\n\n
Using the sequence via the identity column example #<\/a><\/h3>\n\n\n\n
From Oracle 12c, you can associate a sequence with a table column via the identity column<\/a>.<\/p>\n\n\n\n
First, drop<\/a> the tasks<\/code> table:<\/p>\n\n\n
DROP<\/span> TABLE<\/span> tasks;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, recreate the tasks<\/code> table using the identity column for the id<\/code> column:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> tasks(\n    id<\/span> NUMBER<\/span> GENERATED<\/span> ALWAYS<\/span> AS<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>,\n    title VARCHAR2<\/span>(255<\/span>) NOT<\/span> NULL<\/span>\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Behind the scenes, Oracle creates a sequence that is associated with the id<\/code> column of the tasks<\/code> table.<\/p>\n\n\n\n
Because Oracle generated the sequence automatically for the id<\/code> column, in your Oracle instance, the name of the sequence may be different.<\/p>\n\n\n\n
\"Oracle<\/figure>\n\n\n\n
Oracle uses the sys.idnseq$<\/code> to store the link between the table and the sequence.<\/p>\n\n\n\n
This query returns the association of the tasks<\/code> table and ISEQ$$_74366<\/code> sequence:<\/p>\n\n\n
SELECT<\/span> \n    a.name AS<\/span> table_name,\n    b.name AS<\/span> sequence_name\nFROM<\/span>   \n    sys.idnseq$ c\n    JOIN<\/span> obj$ a ON<\/span> c.obj# = a.obj#<\/span>\n    JOIN<\/span> obj$ b ON<\/span> c.seqobj# = b.obj#<\/span>\nWHERE<\/span> \n    a.name = 'TASKS'<\/span>;  <\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, insert some rows into the tasks<\/code> table:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> tasks(title)\nVALUES<\/span>('Learn Oracle identity column in 12c'<\/span>);\n\nINSERT<\/span> INTO<\/span> tasks(title)\nVALUES<\/span>('Verify contents of the tasks table'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, query data from the tasks<\/code> table:<\/p>\n\n\n
SELECT<\/span>\n    id<\/span>, title\nFROM<\/span>\n    tasks;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"Oracle<\/figure>\n\n\n\n
In this tutorial, you have learned how to use the Oracle CREATE SEQUENCE<\/code> statement to create a new sequence in the database.<\/p>\n\n\n\n
Quiz #<\/a><\/h2>\n\n\n\n
Finally, query<\/a> data from the tasks<\/code> table:<\/p>\n\n\n
Third, insert<\/a> data into the tasks<\/code> table:<\/p>\n\n\n