You can use the CREATE SEQUENCE statement to create a sequence. Syntax:
CREATE SEQUENCE sequence_name
[MINVALUE value | NOMINVALUE]
[MAXVALUE value | NOMAXVALUE]
[START WITH value]
[INCREMENT BY value]
[CACHE value | NOCACHE]
[ORDER | NOORDER]
[CYCLE | NOCYCLE];
Where, MINVALUE and MAXVALUE specify the minimum value and maximum value of the sequence. START WITH specifies the starting value in the sequence. INCREMENT BY specifies the step size of the sequence, which may be positive or negative. Its default value is 1. CACHE is used in high-concurrency scenarios to cache the sequence values and maintain the performance. CYCLE specifies whether the sequence restarts after reaching the specified limit. If it does, specify the maximum or minimum value.
Example: Creating a sequence to generate an auto-increment column in a table
obclient> create table t1(id number not null primary key, name varchar(50) , gmt_create date not null default sysdate);
Query OK, 0 rows affected (0.07 sec)
obclient> create sequence seq_t1 start with 10000 increment by 1 cache 50 nocycle;
Query OK, 0 rows affected (0.04 sec)
obclient> insert into t1(id,name) values(seq_t1.nextval, 'A');
Query OK, 1 row affected (0.02 sec)
obclient> insert into t1(id,name) values(seq_t1.nextval, 'B');
Query OK, 1 row affected (0.00 sec)
obclient> insert into t1(id,name) values(seq_t1.nextval, 'C');
Query OK, 1 row affected (0.00 sec)
obclient> select * from t1;
+-------+------+---------------------+
| ID | NAME | GMT_CREATE |
+-------+------+---------------------+
| 10000 | A | 2020-04-02 18:30:29 |
| 10001 | B | 2020-04-02 18:30:34 |
| 10002 | C | 2020-04-02 18:30:39 |
+-------+------+---------------------+
3 rows in set (0.01 sec)
After a sequence is created, you can use the USER_SEQUENCES, ALL_SEQUENCES, or DBA_SEQUENCES statement to view the sequences you created.
obclient> select * from user_sequences \G
*************************** 1. row ***************************
SEQUENCE_NAME: SEQ_T1
MIN_VALUE: 1
MAX_VALUE: 9999999999999999999999999999
INCREMENT_BY: 1
CYCLE_FLAG: N
ORDER_FLAG: N
CACHE_SIZE: 50
LAST_NUMBER: 10100
1 row in set (0.00 sec)
obclient>