Manage sequences

This topic describes how you can create, view, modify, and delete sequences in Oracle mode.

In OceanBase Database, a sequence is a database object of Oracle tenants. You can use it to generate unique sequential values for the primary key column.

A sequence provides two pseudo-columns: CURRVAL and NEXTVAL, which return the current sequence value and the next sequence value respectively. Each query increments the value of NEXTVAL and CURRVAL.

Create a sequence

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];

Information about the parameters:

• MINVALUE and MAXVALUE specify the minimum and maximum values in the sequence.

• START WITH specifies the start value of the sequence.

• INCREMENT BY specifies the increment step. It can be negative. Default value: 1.

• The CACHE option enables caching, so that some values in the sequence are cached when there are many concurrent requests.

  Note

  When you use sequence in a distributed system, if you use the CACHE and NOORDER options at the same time, the sequence numbers that you obtain from connecting to different nodes are unique, but do not increase orderly.

• The CYCLE option specifies whether the values loop in the sequence. For a sequence with looping values, you must specify a minimum and a maximum value.

For more information about the CREATE SEQUENCE statement, see the CREATE SEQUENCE topic in OceanBase Database SQL Reference (Oracle mode).

Example: Creating a sequence to create an auto-increment column

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)

View sequences

You can query the USER_SEQUENCES, ALL_SEQUENCES, and DBA_SEQUENCES views to view sequences.

Example:

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>

Modify a sequence

After you create a sequence, you can use the ALTER SEQUENCE statement to modify the attributes of the sequence.

OceanBase Database allows you to modify the minimum value, maximum value, and increment step of a sequence and enable or disable looping. You cannot modify the start value of a sequence. To modify a sequence, use the following syntax:

ALTER SEQUENCE sequence_name
[MINVALUE value | NOMINVALUE]
[MAXVALUE value | NOMAXVALUE]
[INCREMENT BY value]
[CACHE value | NOCACHE]
[ORDER | NOORDER]
[CYCLE | NOCYCLE];

For more information about the ALTER SEQUENCE statement, see the ALTER SEQUENCE topic in OceanBase Database SQL Reference (Oracle mode).

For example, the following statement specifies the maximum value of the my_sequence auto-increment column. When the maximum value is reached, the column starts another cycle of auto-increment value generation.

obclient> ALTER SEQUENCE my_sequence MAXVALUE 1024 CYCLE;

Delete a sequence

You can use the DROP SEQUENCE statement to delete a sequence. Syntax:

DROP SEQUENCE sequence_name;

For more information about the DROP SEQUENCE statement, see the DROP SEQUENCE topic in OceanBase Database SQL Reference (Oracle mode)

Example: Deleting the S1 sequence

obclient> DROP SEQUENCE S1;
Query OK, 0 rows affected (0.16 sec)