Manage sequences

This topic describes how to create, query, modify, and drop 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 reference to NEXTVAL will update the value of 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];

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.

• CACHE specifies the number of sequence values to be cached for faster access. We recommend that you specify this parameter in high concurrency scenarios.

  Note

  When you use sequences 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.

• CYCLE specifies whether the values of the sequence are cyclically generated. If sequence values are cyclically generated, you must specify a minimum value and a maximum value.

For more information about the CREATE SEQUENCE statement, see CREATE SEQUENCE.

Example: Create a sequence to set a column as 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

obclient> CREATE SEQUENCE seq_t1 START WITH 10000 INCREMENT BY 1 CACHE 50 NOCYCLE;
Query OK, 0 rows affected

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)

Query sequences

You can use the USER_SEQUENCES, ALL_SEQUENCES, and DBA_SEQUENCES views to query 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>

• For more information about the fields in the DBA_SEQUENCES view, see DBA_SEQUENCES.

• For more information about the fields in the ALL_SEQUENCES view, see ALL_SEQUENCES.

• For more information about the fields in the USER_SEQUENCES view, see USER_SEQUENCES.

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, increment step, and cycling flag of a sequence. 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 ALTER SEQUENCE.

Example: Change the maximum value of the my_sequence auto-increment column and enable cyclic generation of auto-increment values.

obclient> ALTER SEQUENCE my_sequence MAXVALUE 1024 CYCLE;

Drop a sequence

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

DROP SEQUENCE sequence_name;

For more information about the DROP SEQUENCE statement, see DROP SEQUENCE.

Example: Drop a sequence named S1.

obclient> DROP SEQUENCE S1;
Query OK, 0 rows affected