Column operations|V4.3.5| docs|Distributed Database

Column operations

Last Updated：2025-11-14 07:33:32 Updated

In OceanBase Database's Oracle mode, column operations include adding columns at the end, dropping columns, renaming columns, modifying column types, managing default values, managing constraints, and setting auto-increment values.

Add columns at the end

The syntax for adding a column at the end 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 constraints of the column to be added.

For more information, see ALTER TABLE.

Assuming there is a table named tbl1 in the database with the following schema:

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

As an example, here we demonstrate how to add a new column C4 to the end of the tbl1 table.

obclient> ALTER TABLE tbl1 ADD C4 INT;

Run the DESCRIBE tbl1; command again to view the schema of the tbl1 table. As shown in the following output, 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

The DROP COLUMN operation is an online DDL operation. It is implemented as a "mark-for-deletion" process, meaning it does not trigger data reorganization. The disk space occupied by the dropped column will not be reclaimed, and the column will still exist in the schema but is marked as deprecated. To completely remove the deprecated column and its associated data and information from the schema, you need to execute the command ALTER TABLE TABLE_NAME FORCE;.

Note

For OceanBase Database V4.3.5, the DROP COLUMN online DDL operation was introduced from V4.3.5 BP1.

Considerations

Considerations for dropping columns are as follows:

To maintain internal referential integrity and avoid potential name conflicts, OceanBase Database renames the deprecated column to SYS_C[COLUMN_ID]_TIME$. Therefore, when adding a new column, its name must not conflict with the system-generated names of deprecated columns, namely, avoid using the SYS_C[COLUMN_ID]_TIME$ format to ensure uniqueness and prevent conflicts. For more information, see ALL_TAB_COLS.
OceanBase Database imposes a limit on the number of deprecated columns allowed in a table. If the number of deprecated columns exceeds 128, adding or deleting columns will no longer be possible. Users must execute ALTER TABLE TABLE_NAME FORCE; to clean up these deprecated columns.
When a column deletion operation is combined with other DDL operations, it becomes an offline DDL operation. In this case, the database performs a physical deletion to completely remove the column from the table schema and storage.
In OceanBase Database, the maximum length of a single row is limited to 1.5 MB. If a table has reached this limit and some columns at the end of the table are marked as deprecated, these columns will still occupy physical storage space even if they are no longer in use. If users plan to add new columns to the end of such a table, they must first execute the command ALTER TABLE TABLE_NAME FORCE; to remove the deprecated columns and reclaim the associated space before adding new columns.
The following offline DDL operations will trigger data reorganization and simultaneously clean up deprecated columns and their associated data and information in the schema, achieving the same effect as executing ALTER TABLE TABLE_NAME FORCE;:
- Add or drop primary keys
- Perform hybrid column operations
- Modify partitioning rules
- Add auto-increment columns
When there are deprecated columns in a table, the following operations cannot be performed: partition exchange, partition-level INSERT OVERWRITE, or full partition-level direct load operations.
When a table contains deprecated columns (marked for deletion), performing a TRUNCATE operation on the table will not remove the columns that have been marked for deletion.

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 column is as follows:

ALTER TABLE table_name DROP COLUMN column_name;

Here, 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.

Assuming there is a table named tbl1 in the database with the following schema:

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

As an example, here we demonstrate how to drop the C3 column from the tbl1 table:

obclient> ALTER TABLE tbl1 DROP COLUMN C3;

Execute DESCRIBE tbl1; again to view the table structure of tbl1. The result 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

You can execute either of the following statements to drop multiple columns:

Method 1:

ALTER TABLE table_name DROP (column_name1, column_name2, ...);

Method 2:

ALTER TABLE table_name DROP COLUMN column_name1, DROP COLUMN column_name2, ...;

Similar to dropping a single column, table_name specifies the name of the table containing the columns to be dropped, and column_name specifies the names of the columns to be dropped.

Assume there is a table tbl1 in the database, and its schema 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  |
+-------+--------------+------+------+---------+-------+

As an example, here we drop the C4 and C5 columns from the tbl1 table to demonstrate how to use the ALTER TABLE table_name DROP (column_name1, column_name2, ...) syntax to drop multiple columns from a table.

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

Then, execute the DESCRIBE tbl1; statement again to view the schema of the tbl1 table. As shown in the following output, the C4 and C5 columns no longer exist in 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  |
+-------+--------------+------+------+---------+-------+

Next, we take dropping the C2 and C3 columns from the tbl1 table as an example to demonstrate how to use the ALTER TABLE table_name DROP COLUMN column_name1, DROP COLUMN column_name2, ... syntax to drop multiple columns from a table.

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

Then, execute the DESCRIBE tbl1; statement again to view the schema of the tbl1 table. As shown in the following output, the C2 and C3 columns no longer exist in the tbl1 table.

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

Clean up deprecated columns

The syntax for cleaning up deprecated columns is as follows:

ALTER TABLE TABLE_NAME FORCE;

Note

For OceanBase Database V4.3.5, the feature to clean up deprecated columns is supported from V4.3.5 BP1.

Here, we take cleaning up the deprecated columns C2, C3, C4, and C5 from the tbl1 table as an example to demonstrate how to remove deprecated columns from a 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 where the target column to be renamed is located.
old_col_name: the name of the target column to be renamed.
new_col_name: the new name of the column after it is renamed.

Assuming there is a table named tbl1 in the database with the following schema:

+-------+--------------+------+------+---------+-------+
| 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 in the tbl1 table:

obclient> ALTER TABLE tbl1 RENAME COLUMN C3 TO C4;

Execute DESCRIBE tbl1; again to view the table schema of tbl1. The result is as follows, showing that the C3 column in the tbl1 table is 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 column type

OceanBase Database supports data type conversion between the following column types:

Character data types: CHAR and VARCHAR2
Numeric data types: NUMBER (precision can be only increased)
Character data types: CHAR (precision can be only increased), VARCHAR2, NVARCHAR2, and NCHAR

For more information about column type change rules 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 where the target column to be modified is located.
column_name: the name of the column to be modified.
data_type: the new data type.

Examples

Data type conversion between character types

The following example creates a table named test01:

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

Take the test01 table as an example. The following examples show how to change the data type and length of a column of the character type.

Change the length 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 to VARCHAR and set the maximum length to 20 characters.
```
obclient> ALTER TABLE test01 MODIFY C2 VARCHAR(20);
```
Change the maximum length of the C3 column to 64 characters.
```
obclient> ALTER TABLE test01 MODIFY C3 VARCHAR(64);
```
Change the maximum length of the C3 column to 16 characters.
```
obclient> ALTER TABLE test01 MODIFY C3 VARCHAR(16);
```
Change the data type of the C3 column to CHAR and set the length to 256 characters.
```
obclient> ALTER TABLE test01 MODIFY C3 CHAR(256);
```

Precision modification of a numeric data type

The following example creates a table named test02:

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

Take the test02 table as an example. The following example shows how to change the precision of a column of the numeric data type.

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

Manage default values for columns

If you do not set a default value, the default value for a column is NULL. The syntax 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 where the target column to be modified is located.
column_name: the name of the target column to be modified.
data_type: the data type of the target column. You can specify the current data type or change the column data type to another supported type. For more information, see [Modify the column type](#Modify the column type).
const_value: the default value of the target column after modification.

Assume that a table named tbl1 exists in the database and the structure of the table 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 the C1 column to 111.
```
obclient> ALTER TABLE tbl1 MODIFY C1 NUMBER(10) DEFAULT 111;
```
You can execute the following statement to drop the default value of the C3 column.
```
obclient> ALTER TABLE tbl1 MODIFY C3 NUMBER(3) DEFAULT NULL;
```

After you execute the DESCRIBE tbl1; statement, the output is as follows: the default value of the C1 column in the tbl1 table 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 columns, such as modifying an existing column to an auto-increment column, allowing or disallowing null values in a column, and specifying the uniqueness of a column. This section will guide you through the process.

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 where the target column is located.
column_name: the name of the target column.
data_type: the data type of the target column. You can specify the current data type or another data type to modify the column. For more information, see the previous section Modify column type.
NULL | NOT NULL: specifies that values can be null (NULL) or cannot be null (NOT NULL) in the selected column.
PRIMARY KEY: specifies that the selected column be the primary key.
UNIQUE: specifies that the values in the selected column be unique.

Assume that a table named tbl1 exists in the database and the schema of the tbl1 table 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 allow null values but to contain unique values.
```
obclient> ALTER TABLE tbl1 MODIFY C3 NUMBER(3) UNIQUE;
```

Execute the DESCRIBE tbl1; statement again to view the schema of 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  | UNI | NULL    | NULL  |
+-------+--------------+------+-----+---------+-------+

Set the auto-increment column value

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

Here is an example of how to manage auto-increment column values:

Create a sequence named seq1, set the start value to 1, the increment to 1, and the 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);

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

obclient> SELECT * FROM tbl1;

The output is as follows. The values in the C1 column increase by 1.

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

Manually trigger an 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 to view the data in the tbl1 table again.

obclient> SELECT * FROM tbl1;

The output is as follows. There is no row with the value 4 in the C1 column. Instead, the value 5 is directly inserted.

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

References

ALTER TABLE