Manage sequences

This topic describes how to create, query, modify, and drop sequences in the Oracle mode of OceanBase Database.

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. The syntax is as follows:

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 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. The default value is 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.

Here is an 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 change the start value, minimum value, maximum value, increment step, and cycle of a sequence. The SQL syntax for modifying a sequence is as follows:

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

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

Here is an example:

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

   obclient> ALTER SEQUENCE seq2 MAXVALUE 1024 CYCLE;
   

2. Change 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 change the start value of a sequence, RESTART does not necessarily need to be behind the sequence name. It can be at any position in the statement. A sample statement is as follows:

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

Drop a sequence

You can use the DROP SEQUENCE statement to drop a sequence. The syntax is as follows:

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