Column operations|V4.2.5| docs|Distributed Database

Column operations

Last Updated：2026-02-11 07:43:39 Updated

In OceanBase Database in Oracle mode, you can add a column at the end of a table, drop a column, rename a column, modify the data type of a column, manage the default value of a column, manage constraints, and set the value of an auto-increment column.

Add a column at the end of a table

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

ALTER TABLE table_name ADD column_name column_definition;

The following table describes the parameters.

table_name: specifies the name of the table to which you want to add a column.
column_name: specifies the name of the column to be added.
column_definition: specifies the data type and constraints of the column to be added.

For more information, see ALTER TABLE.

Assume that a table named tbl1 exists in the database. The table structure 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 shows how to add a column named C4 at the end of the tbl1 table.

obclient> ALTER TABLE tbl1 ADD C4 INT;

Execute the DESCRIBE tbl1; command again to view the table structure of tbl1. The output is as follows, indicating 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 a column

DROP COLUMN is an Online DDL operation. It marks the column for deletion without triggering data reorganization. This means that the disk space occupied by the deleted column is not reclaimed, and the column still exists in the schema, but is marked as obsolete. To completely remove the obsolete column and its data and information from the schema, execute the ALTER TABLE TABLE_NAME FORCE; command.

Considerations

When dropping a column, note the following:

OceanBase Database modifies the name of an obsolete column to SYS_C[COLUMN_ID]_TIME$ to maintain internal referential integrity and avoid potential name conflicts. Therefore, the name of a newly added column cannot be in the SYS_C[COLUMN_ID]_TIME$ format to ensure uniqueness and avoid potential conflicts. For more information about column naming, see ALL_TAB_COLS.
OceanBase Database limits the maximum number of obsolete columns in a table to 128. If the number of obsolete columns exceeds 128, you cannot add or drop columns. To resolve this issue, execute the ALTER TABLE TABLE_NAME FORCE; command to remove the obsolete columns.
When a DDL operation for dropping a column is combined with other operations, the database performs a physical deletion to ensure that the column is completely removed from the table structure and storage.
In OceanBase Database, the maximum length of a single data row is 1.5 MB. If the length of a row reaches this limit and some columns at the end of the table are marked as obsolete, even if these columns are no longer used, they still occupy physical storage space. If you plan to add new columns at the end of the table, you must first execute the ALTER TABLE TABLE_NAME FORCE; command to remove the obsolete columns and reclaim the space before adding new columns.
The following Offline DDL operations, like the ALTER TABLE TABLE_NAME FORCE; command, trigger data reorganization and remove obsolete columns and their data and information from the schema:
- Modify the primary key
- Perform mixed column operations
- Modify the partitioning rule
- Add an auto-increment column
- TRUNCATE the table

For more information about Online DDL and Offline DDL operations, see Online DDL 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;

In this syntax, table_name specifies the name of the table from which you want to drop a column, and column_name specifies the name of the column to be dropped.

Assume that a table named tbl1 exists in the database. The table structure 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 shows how to drop the C3 column from the tbl1 table.

obclient> ALTER TABLE tbl1 DROP COLUMN C3;

Execute the DESCRIBE tbl1; command again to view the table structure of tbl1. The output is as follows, indicating that the C3 column has been dropped from the tbl1 table.

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

Drop multiple columns

The syntax for dropping multiple columns is as follows:

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

In both methods, table_name specifies the name of the table from which you want to drop columns, and column_name specifies the name of each column to be dropped. Assume that a table named tbl1 exists in the database. The table structure 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 shows how to use the ALTER TABLE table_name DROP (column_name1, column_name2, ...) syntax to drop the C4 and C5 columns from the tbl1 table.

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

Execute the DESCRIBE tbl1; command again to view the table structure of tbl1. The output is as follows, indicating that the C4 and C5 columns have been dropped from the tbl1 table.

+-------+--------------+------+------+---------+-------+
| 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 shows how to use the ALTER TABLE table_nameE DROP COLUMN column_name1, DROP COLUMN column_name2, ... syntax to drop the C2 and C3 columns from the tbl1 table.

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

Execute the DESCRIBE tbl1; command again to view the table structure of tbl1. The output is as follows, indicating that the C2 and C3 columns have been dropped from the tbl1 table.

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

Drop obsolete columns

The syntax for dropping obsolete columns is as follows:

ALTER TABLE TABLE_NAME FORCE;

The following example shows how to drop 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 following table describes the parameters of the preceding statement.

table_name: specifies the name of the table that contains the column to be renamed.
old_col_name: specifies the name of the column to be renamed.
new_col_name: specifies the new name of the column.

Assume that a table named tbl1 exists in the database. The table structure 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 shows how to rename the C3 column to C4.

obclient> ALTER TABLE tbl1 RENAME COLUMN C3 TO C4;

Execute the DESCRIBE tbl1; statement again to view the table structure. The output is as follows, indicating that the C3 column has been renamed to 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  |
+-------+--------------+------+------+---------+-------+

Modify the column type

The following table describes the column type conversion rules supported by OceanBase Database.

Conversion between character data types, including CHAR and VARCHAR2.
Change the precision of numeric data types, including NUMBER (precision cannot be reduced).
Change the precision of character data types, including CHAR (precision cannot be reduced), VARCHAR2, NVARCHAR2, and NCHAR.

For more information about column type conversion rules in Oracle mode, see Column type conversion rules.

The syntax for modifying the column type is as follows:

ALTER TABLE table_name MODIFY column_name data_type;

The following table describes the parameters of the preceding statement.

table_name: specifies the name of the table that contains the column to be modified.
column_name: specifies the name of the column to be modified.
data_type: specifies the new data type.

Examples

Convert between character data types

The following example shows how to create a table named test01.

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

The following example shows how to modify the data type and length of a character data type column in the test01 table.

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

Change the precision of a numeric data type

The following example shows how to create a table named test02.

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

The following example shows how to modify the precision of a numeric data type column in the test02 table.

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

Manage the default value of a column

If no default value is specified, the default value of a column is NULL. 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 following table describes the parameters of the preceding statement.

table_name: specifies the name of the table that contains the column to be modified.
column_name: specifies the name of the column to be modified.
data_type: specifies the data type of the column to be modified. You can specify the current data type or change the column to another data type. For information about the data types that can be changed, see Modify the column type.
const_value: specifies the default value of the column.

Assume that a table named tbl1 exists in the database. The table structure 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  |
+-------+--------------+------+------+---------+-------+

You can execute the following statement to set the default value of a column. In this example, the default value of the C1 column is set to 111.
```
obclient> ALTER TABLE tbl1 MODIFY C1 NUMBER(10) DEFAULT 111;
```
You can execute the following statement to remove the default value of a column. In this example, the default value of the C3 column is removed.
```
obclient> ALTER TABLE tbl1 MODIFY C3 NUMBER(3) DEFAULT NULL;
```

Execute the DESCRIBE tbl1; statement again to view the table structure. The output is as follows, indicating that the default value of the C1 column is 111 and the default value of the C3 column 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 constraints to a table, such as modifying an existing column to an auto-increment column, setting whether a column can be null, or specifying the uniqueness of a column. This section describes how to perform these operations.

The syntax for managing constraints is as follows:

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

The following table describes the parameters of the preceding statement.

table_name: specifies the name of the table that contains the column to which a constraint is to be added.
column_name: specifies the name of the column to which a constraint is to be added.
data_type: specifies the data type of the column to be modified. You can specify the current data type or change the column to another data type. For information about the data types that can be changed, see Modify the column type.
NULL | NOT NULL: specifies whether the selected column can be null (NULL) or cannot be null (NOT NULL).
PRIMARY KEY: specifies that the selected column is a primary key.
UNIQUE: specifies the uniqueness of the selected column.

Assume that a table named tbl1 exists in the database. The table structure 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.

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

Set the C2 column to not allow null values.

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

Set the C3 column to be unique.

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

Execute the DESCRIBE tbl1; statement again to view the table structure.

+-------+--------------+------+-----+---------+-------+
| 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  |
+-------+--------------+------+-----+---------+-------+

Set the value of an auto-increment column

To set the value of an auto-increment column, you must use the CREATE SEQUENCE statement to create an auto-increment field, and then use the nextval function to retrieve the next value from the sequence.

Here is an example of managing the value of an auto-increment column:

Create an auto-increment sequence named seq1 with a starting value of 1, an increment of 1, and a cache size of 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);

Execute the following command to view the data in the tbl1 table.

obclient> SELECT * FROM tbl1;

The output is as follows, with the C1 column values incrementing from 1.

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

Manually trigger the auto-increment of the seq1 sequence to modify the auto-increment column value.
```
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);

Execute the following command again to view the data in the tbl1 table.

obclient> SELECT * FROM tbl1;

The output is as follows, with no row having a value of 4 in the C1 column, and the value 5 is directly inserted.

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

References

ALTER TABLE