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

Statement usage notes:

• To execute this statement, you must have the CREATE SEQUENCE privilege.

• MINVALUE and MAXVALUE specify the minimum and maximum values of 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.

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

obclient> INSERT INTO t1(id,name) VALUES(seq_t1.nextval, 'B');
Query OK, 1 row affected

obclient> INSERT INTO t1(id,name) VALUES(seq_t1.nextval, 'C');
Query OK, 1 row affected

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

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: SEQ1
    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

• 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 start value, minimum value, maximum value, increment step, and cycling attribute of a sequence. SQL syntax for modifying a sequence:

ALTER SEQUENCE sequence_name
  {
  [RESTART]
  |[START WITH int_value]
  |[ MINVALUE value | NOMINVALUE ]
  |[ MAXVALUE value | NOMAXVALUE ]
  |[ INCREMENT BY value ]
  |[ CACHE value | NOCACHE ]
  |[ ORDER | NOORDER ]
  |[ CYCLE | NOCYCLE]
  };

To execute this statement, you must have the ALTER privilege over the object or the ALTER ANY SEQUENCE system privilege.

Examples:

1. Modify the maximum value of the seq2 sequence and enable cyclic generation of sequence values.

   obclient> ALTER SEQUENCE seq2 MAXVALUE 1024 CYCLE;
   

2. Modify the start value of the sequence.

   • If the value of INCREMENT BY is greater than 0, you can reset the sequence to make the sequence start from MINVALUE. If the value of INCREMENT BY is less than 0, you can execute the following statement to reset the sequence to make the sequence start from MAXVALUE:

     obclient> ALTER SEQUENCE seq3 RESTART;
     Query OK, 0 rows affected
     

   • Execute the following statement to reset the sequence and make the sequence start from 3:

     obclient> ALTER SEQUENCE seq4 RESTART START WITH 3;
     Query OK, 0 rows affected
     

   When you modify the start value of a sequence, RESTART is not necessarily placed after the sequence name. It can be placed anywhere in the statement, as shown in the following example:

   obclient> ALTER SEQUENCE seq4 START WITH 3 RESTART MINVALUE -100 CYCLE;
   Query OK, 0 rows affected
   

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

Drop a sequence

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

DROP SEQUENCE sequence_name;

To execute this statement, you must have the DROP privilege over the object or the DROP ANY SEQUENCE system privilege.

Example: Drop the auto-increment column seq5.

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

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