Column operations|V4.2.5| docs|Distributed Database

Column operations

Last Updated：2025-11-19 10:08:12 Updated

This topic describes the column operations in Oracle mode of OceanBase Database, including adding a column to the end of a table, dropping a column, renaming a column, changing the data type of a column, managing the default value of a column, managing constraints, and changing the value of an auto-increment column.

Add a column to the end of a table

The syntax for adding a column to the end of a table is as follows:

ALTER TABLE table_name ADD column_name column_definition;

The parameters are described as follows:

table_name: the name of the table to which the column is to be added.
column_name: the name of the column to be added.
column_definition: the data type and constraint of the column to be added.

For more information, see ALTER TABLE.

Assume that the tbl1 table exists in the database and the table schema of tbl1 is as follows:

+-------+--------------+------+------+---------+-------+
| FIELD | TYPE         | NULL | KEY  | DEFAULT | EXTRA |
+-------+--------------+------+------+---------+-------+
| C1    | NUMBER(10)   | NO   | PRI  | NULL    | NULL  |
| C2    | VARCHAR2(50) | NO   | NULL | NULL    | NULL  |
| C3    | NUMBER(3)    | YES  | NULL | NULL    | NULL  |
+-------+--------------+------+------+---------+-------+

The following example adds the C4 column to the end of the tbl1 table.

obclient> ALTER TABLE tbl1 ADD C4 INT;

Execute DESCRIBE tbl1; again to view the table schema of tbl1. The output is as follows, showing that the C4 column has been added to the tbl1 table:

+-------+--------------+------+------+---------+-------+
| FIELD | TYPE         | NULL | KEY  | DEFAULT | EXTRA |
+-------+--------------+------+------+---------+-------+
| C1    | NUMBER(10)   | NO   | PRI  | NULL    | NULL  |
| C2    | VARCHAR2(50) | NO   | NULL | NULL    | NULL  |
| C3    | NUMBER(3)    | YES  | NULL | NULL    | NULL  |
| C4    | NUMBER(38)   | YES  | NULL | NULL    | NULL  |
+-------+--------------+------+------+---------+-------+

Drop columns

DROP COLUMN is an online DDL operation, which will not trigger data reorganization. The dropped column is deprecated and the disk space occupied by the dropped column will not be reclaimed. To delete a deprecated column and its data and information from the schema, execute the ALTER TABLE TABLE_NAME FORCE; statement.

Considerations

When you drop a column, observe the following considerations:

To ensure internal reference integrity and avoid potential name conflicts, OceanBase Database will rename a deprecated column in the format of SYS_C[COLUMN_ID]_TIME$. The name of a new column must not be the same as the name generated by the system for a deprecated column. In other words, new columns must not be named in the format of SYS_C[COLUMN_ID]_TIME$ to ensure name uniqueness and avoid potential name conflicts.
OceanBase Database limits the maximum number of deprecated columns allowed in a table. When the number of deprecated columns exceeds 128, you cannot add or drop columns. In this case, you must execute the ALTER TABLE TABLE_NAME FORCE; statement to clear these deprecated columns.
When DROP COLUMN is used in combination with other operations, the database will physically delete the target column to ensure that it is completely removed from the table schema and storage.
In OceanBase Database, the maximum length of a single data row is limited to 1.5 MB. In a table that has reached this length limit, if some columns at the end of the table are marked as deprecated, they still occupy physical storage space even if they are no longer used. If you want to add new columns at the end of this table, you must first execute the ALTER TABLE TABLE_NAME FORCE; statement to clear the deprecated columns to reclaim the storage space.
The following offline DDL operations will trigger data reorganization and reclaim the disk space occupied by deprecated columns, but will not remove related information from the schema. To delete a deprecated column and its data and information from the schema, execute the ALTER TABLE TABLE_NAME FORCE; statement.
- Add, drop, or modify a primary key
- Hybrid column operations
- Modify partitioning rules
- Add an auto-increment column
- Truncate a table

For more information about online and offline DDL operations, see Online and offline DDL operations.

Drop a single column

The syntax for dropping a single column is as follows:

ALTER TABLE table_name DROP COLUMN column_name;

table_name specifies the name of the table to which the column belongs, and column_name specifies the name of the column to be dropped.

Assume that the tbl1 table exists in the database and the table schema of tbl1 is as follows:

+-------+--------------+------+------+---------+-------+
| FIELD | TYPE         | NULL | KEY  | DEFAULT | EXTRA |
+-------+--------------+------+------+---------+-------+
| C1    | NUMBER(10)   | NO   | PRI  | NULL    | NULL  |
| C2    | VARCHAR2(50) | NO   | NULL | NULL    | NULL  |
| C3    | NUMBER(3)    | YES  | NULL | NULL    | NULL  |
+-------+--------------+------+------+---------+-------+

The following example drops the C3 column from the tbl1 table.

obclient> ALTER TABLE tbl1 DROP COLUMN C3;

Execute DESCRIBE tbl1; again to view the table schema of tbl1. The output is as follows, showing that the tbl1 table no longer has the C3 column:

+-------+--------------+------+------+---------+-------+
| FIELD | TYPE         | NULL | KEY  | DEFAULT | EXTRA |
+-------+--------------+------+------+---------+-------+
| C1    | NUMBER(10)   | NO   | PRI  | NULL    | NULL  |
| C2    | VARCHAR2(50) | NO   | NULL | NULL    | NULL  |
+-------+--------------+------+------+---------+-------+

Drop multiple columns

The syntaxes for dropping multiple columns are as follows:

-- Syntax 1
ALTER TABLE table_name DROP (column_name1, column_name2, ...);
-- Syntax 2
ALTER TABLE table_nameE DROP COLUMN column_name1, DROP COLUMN column_name2, ...;

table_name specifies the name of the table to which the column belongs, and column_name specifies the name of the column to be dropped. Assume that the tbl1 table exists in the database and the table schema of tbl1 is as follows:

+-------+--------------+------+------+---------+-------+
| FIELD | TYPE         | NULL | KEY  | DEFAULT | EXTRA |
+-------+--------------+------+------+---------+-------+
| C1    | NUMBER(30)   | NO   | PRI  | NULL    | NULL  |
| C2    | VARCHAR2(50) | YES  | NULL | NULL    | NULL  |
| C3    | NUMBER(30)   | YES  | NULL | NULL    | NULL  |
| C4    | NUMBER(30)   | YES  | NULL | NULL    | NULL  |
| C5    | NUMBER(30)   | YES  | NULL | NULL    | NULL  |
+-------+--------------+------+------+---------+-------+

The following example drops the C4 and C5 columns from the tbl1 table by using the ALTER TABLE table_name DROP (column_name1, column_name2, ...) syntax.

obclient> ALTER TABLE tbl1 DROP COLUMN (C4, C5);

Execute DESCRIBE tbl1; again to view the table schema of tbl1. The output is as follows, showing that the tbl1 table no longer has the C4 and C5 columns:

+-------+--------------+------+------+---------+-------+
| FIELD | TYPE         | NULL | KEY  | DEFAULT | EXTRA |
+-------+--------------+------+------+---------+-------+
| C1    | NUMBER(30)   | NO   | PRI  | NULL    | NULL  |
| C2    | VARCHAR2(50) | YES  | NULL | NULL    | NULL  |
| C3    | NUMBER(30)   | YES  | NULL | NULL    | NULL  |
+-------+--------------+------+------+---------+-------+

The following example drops the C2 and C3 columns from the tbl1 table by using the ALTER TABLE table_nameE DROP COLUMN column_name1, DROP COLUMN column_name2, ... syntax.

obclient> ALTER TABLE tbl1 DROP COLUMN C2, DROP COLUMN C3;

Execute DESCRIBE tbl1; again to view the table schema of tbl1. The output is as follows, showing that the tbl1 table no longer has the C2 and C3 columns:

+-------+--------------+------+------+---------+-------+
| FIELD | TYPE         | NULL | KEY  | DEFAULT | EXTRA |
+-------+--------------+------+------+---------+-------+
| C1    | NUMBER(30)   | NO   | PRI  | NULL    | NULL  |
+-------+--------------+------+------+---------+-------+

Clear deprecated columns

The syntax for clearing deprecated columns is as follows:

ALTER TABLE TABLE_NAME FORCE;

The following example clears the C2, C3, C4, and C5 columns from the tbl1 table.

obclient> ALTER TABLE tbl1 FORCE;

Rename a column

The syntax for renaming a column is as follows:

ALTER TABLE table_name RENAME COLUMN old_col_name TO new_col_name;

The parameters are described as follows:

table_name: the name of the table to which the column belongs.
old_col_name: the original name of the column.
new_col_name: the new name of the column.

Assume that the tbl1 table exists in the database and the table schema of tbl1 is as follows:

+-------+--------------+------+------+---------+-------+
| FIELD | TYPE         | NULL | KEY  | DEFAULT | EXTRA |
+-------+--------------+------+------+---------+-------+
| C1    | NUMBER(10)   | NO   | PRI  | NULL    | NULL  |
| C2    | VARCHAR2(50) | NO   | NULL | NULL    | NULL  |
| C3    | NUMBER(3)    | YES  | NULL | NULL    | NULL  |
+-------+--------------+------+------+---------+-------+

The following example renames the C3 column as C4.

obclient> ALTER TABLE tbl1 RENAME COLUMN C3 TO C4;

Execute DESCRIBE tbl1; again to view the table schema of tbl1. The output is as follows, showing that the C3 column in the tbl1 table has been renamed as C4:

+-------+--------------+------+------+---------+-------+
| FIELD | TYPE         | NULL | KEY  | DEFAULT | EXTRA |
+-------+--------------+------+------+---------+-------+
| C1    | NUMBER(10)   | NO   | PRI  | NULL    | NULL  |
| C2    | VARCHAR2(50) | NO   | NULL | NULL    | NULL  |
| C4    | NUMBER(3)    | YES  | NULL | NULL    | NULL  |
+-------+--------------+------+------+---------+-------+

Change the data type of a column

OceanBase Database supports the following data type conversions:

Data type conversion for character data types CHAR and VARCHAR2.
Precision change for the numeric data type NUMBER. Note that you can only increase the precision.
Precision change for character data types CHAR (only precision increase supported), VARCHAR2, NVARCHAR2, and NCHAR.

For more information about the rules for changing the data types of columns in Oracle mode of OceanBase Database, see Column type change rules.

The syntax for changing the data type of a column is as follows:

ALTER TABLE table_name MODIFY column_name data_type;

The parameters are described as follows:

table_name: the name of the table to which the column belongs.
column_name: the name of the column whose data type is to be changed.
data_type: the new data type.

Examples of column data type changes

Conversions between character data types

Create a table named test01 as follows:

obclient> CREATE TABLE test01 (C1 INT PRIMARY KEY, C2 CHAR(10), C3 VARCHAR2(32));

The following examples show how to change the data type and length limit of a column of a character data type.

Change the length limit of the C2 column in the test01 table to 20 characters.
```
obclient> ALTER TABLE test01 MODIFY C2 CHAR(20);
```
Change the data type of the C2 column in the test01 table to VARCHAR, and set the length limit to 20 characters.
```
obclient> ALTER TABLE test01 MODIFY C2 VARCHAR(20);
```
Change the length limit of the C3 column in the test01 table to 64 characters.
```
obclient> ALTER TABLE test01 MODIFY C3 VARCHAR(64);
```
Change the length limit of the C3 column in the test01 table to 16 characters.
```
obclient> ALTER TABLE test01 MODIFY C3 VARCHAR(16);
```
Change the data type of the C3 column in the test01 table to CHAR, and set the length limit to 256 characters.
```
obclient> ALTER TABLE test01 MODIFY C3 CHAR(256);
```

Precision change for a numeric data type

Create a table named test02 as follows:

obclient> CREATE TABLE test02(C1 NUMBER(10,2));

The following example shows how to change the precision for a column of a numeric data type that has a precision:

obclient> ALTER TABLE test02 MODIFY C1 NUMBER(11,3);

Manage the default value of a column

If a column is not configured with a default value, its value is NULL by default. The syntax for managing the default value of a column is as follows:

ALTER TABLE table_name MODIFY column_name data_type DEFAULT const_value;

The parameters are described as follows:

table_name: the name of the table to which the column belongs.
column_name: the name of the column whose default value is to be changed.
data_type: the new data type of the column. You can specify the current data type or another data type. For more information, see the Change the data type of a column section.
const_value: the new default value of the column.

Assume that the tbl1 table exists in the database and the table schema of tbl1 is as follows:

+-------+--------------+------+------+---------+-------+
| FIELD | TYPE         | NULL | KEY  | DEFAULT | EXTRA |
+-------+--------------+------+------+---------+-------+
| C1    | NUMBER(10)   | NO   | PRI  | NULL    | NULL  |
| C2    | VARCHAR2(50) | NO   | NULL | NULL    | NULL  |
| C3    | NUMBER(3)    | YES  | NULL | 333     | NULL  |
+-------+--------------+------+------+---------+-------+

The following example changes the default value of the C1 column to 111:
```
obclient> ALTER TABLE tbl1 MODIFY C1 NUMBER(10) DEFAULT 111;
```

The following example drops the default value of the C3 column:

obclient> ALTER TABLE tbl1 MODIFY C3 NUMBER(3) DEFAULT NULL;

Execute DESCRIBE tbl1; again to view the table schema of tbl1. The output is as follows, showing that the default value of C1 is 111 and that of C3 is NULL:

+-------+--------------+------+------+---------+-------+
| FIELD | TYPE         | NULL | KEY  | DEFAULT | EXTRA |
+-------+--------------+------+------+---------+-------+
| C1    | NUMBER(10)   | NO   | PRI  | 111     | NULL  |
| C2    | VARCHAR2(50) | NO   | NULL | NULL    | NULL  |
| C3    | NUMBER(3)    | YES  | NULL | NULL    | NULL  |
+-------+--------------+------+------+---------+-------+

Manage constraints

In Oracle mode of OceanBase Database, you can add column constraints to tables. For example, you can change an existing column to an auto-increment column, set or cancel a NOT NULL constraint for a column, and set or cancel a UNIQUE constraint for a column.

The syntax for managing constraints is as follows:

ALTER TABLE table_name
    MODIFY column_name data_type
    [NULL | NOT NULL]
    [PRIMARY KEY]
    [UNIQUE];

The parameters are described as follows:

table_name: the name of the table to which the column belongs.
column_name: the name of the column to which the constraint is to be added.
data_type: the new data type of the column. You can specify the current data type or another data type. For more information, see the Change the data type of a column section.
NULL | NOT NULL: specifies whether the column can contain null values (NULL) or cannot contain null values (NOT NULL).
PRIMARY KEY: sets the column as the primary key column.
UNIQUE: sets the UNIQUE constraint for the column.

Assume that the tbl1 table exists in the database and the table schema of tbl1 is as follows:

+-------+--------------+------+-----+---------+-------+
| FIELD | TYPE         | NULL | KEY | DEFAULT | EXTRA |
+-------+--------------+------+-----+---------+-------+
| C1    | NUMBER(10)   | YES  | NULL | NULL    | NULL  |
| C2    | VARCHAR2(50) | YES  | NULL | NULL    | NULL  |
| C3    | NUMBER(3)    | YES  | NULL | NULL    | NULL  |
+-------+--------------+------+-----+---------+-------+

Set the C1 column as the primary key column.

obclient> ALTER TABLE tbl1 MODIFY C1 NUMBER(10) PRIMARY KEY;

Set the NOT NULL constraint for the C2 column.

obclient> ALTER TABLE tbl1 MODIFY C2 VARCHAR(50) NOT NULL;

Set the UNIQUE constraint for the C3 column.

obclient> ALTER TABLE tbl1 MODIFY C3 NUMBER(3) UNIQUE;

Execute DESCRIBE tbl1; again to view the table schema of tbl1.

+-------+--------------+------+-----+---------+-------+
| FIELD | TYPE         | NULL | KEY | DEFAULT | EXTRA |
+-------+--------------+------+-----+---------+-------+
| C1    | NUMBER(10)   | NO   | PRI | NULL    | NULL  |
| C2    | VARCHAR2(50) | NO   | NULL | NULL    | NULL  |
| C3    | NUMBER(3)    | YES  | UNI | NULL    | NULL  |
+-------+--------------+------+-----+---------+-------+

Change the value of an auto-increment column

Before you can change the value of an auto-increment column, you must use CREATE SEQUENCE to create an auto-increment column, and then call the nextval function to retrieve the next value from the sequence.

Here is an example of auto-increment column management:

Create an auto-increment sequence named seq1, and set the start value to 1, increment step to 1, and cache size to 10.
```
obclient> CREATE SEQUENCE seq1 MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 10;
```

Insert data into the tbl1 table.

obclient> INSERT INTO tbl1(C1, C2, C3) VALUES (seq1.nextval, 'zhangsan', 20), (seq1.nextval, 'lisi', 21), (seq1.nextval, 'wangwu', 22);

View the data in the tbl1 table.

obclient> SELECT * FROM tbl1;

The output is as follows, showing that the values of the C1 column start from 1:

+------+----------+------+
| C1   | C2       | C3   |
+------+----------+------+
|    1 | zhangsan |   20 |
|    2 | lisi     |   21 |
|    3 | wangwu   |   22 |
+------+----------+------+

Manually call NEXTVAL to increment the value of the seq1 sequence.

obclient> SELECT seq1.nextval FROM sys.dual;

The output is as follows:

+---------+
| NEXTVAL |
+---------+
|      4 |
+---------+

Insert data into the tbl1 table again.

obclient> INSERT INTO tbl1(C1, C2, C3) VALUES (seq1.nextval, 'oceanbase', 12);

View the data in the tbl1 table again.

obclient> SELECT * FROM tbl1;

The output is as follows, showing that the C1 column does not have the value 4 and the sequence resumes from 5.

+------+-----------+------+
| C1   | C2        | C3   |
+------+-----------+------+
|    1 | zhangsan  |   20 |
|    2 | lisi      |   21 |
|    3 | wangwu    |   22 |
|    5 | oceanbase |   12 |
+------+-----------+------+

References

ALTER TABLE