Column operations|V4.2.1| docs|Distributed Database

Column operations

Last Updated：2025-12-02 11:55:53 Updated

In the Oracle mode of OceanBase Database, you can perform operations such as adding columns to the end of a table, dropping columns, renaming columns, modifying the data types of columns, managing default values of columns, managing constraints, and setting auto-increment for columns.

Add a column 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.
column_definition: the data type and constraints of the column.

For more information, see ALTER TABLE.

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

In this example, a column named C4 is added at the end of the tbl1 table.

obclient> ALTER TABLE tbl1 ADD C4 INT;

Run the DESCRIBE tbl1; statement again to view the structure of the tbl1 table. As shown in the 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 that does not trigger a major compaction. The disk space occupied by the dropped column is not recycled. Instead, the column is marked as dropped. To completely drop a column and its metadata and information from the schema, you must execute the ALTER TABLE TABLE_NAME FORCE; statement.

Considerations when dropping a column

When you drop a column, keep in mind the following points:

To maintain internal referential integrity and avoid potential name conflicts, OceanBase Database automatically renames the dropped column to: SYS_C[COLUMN_ID]_TIME$. Therefore, if you add a new column, do not use the system-generated name of the dropped column, namely, do not use the SYS_C[COLUMN_ID]_TIME$ format, to ensure uniqueness and avoid potential conflicts. For more information about column naming conventions, see ALL_TAB_COLS.
OceanBase Database allows a maximum of 128 dropped columns in a table. If more than 128 columns are dropped, you cannot add or drop columns. In this case, you can execute the ALTER TABLE TABLE_NAME FORCE; statement to drop the columns.
If you perform a DDL operation that combines with other operations, the database will perform a physical drop 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 some columns at the end of a table reach the length limit, even if these columns are dropped and no longer used, they still occupy physical storage space. If you want to add new columns at the end of the table, you must execute the ALTER TABLE TABLE_NAME FORCE; statement to drop these columns and recycle the related space before you add new columns.
The following Offline DDL operations will trigger a major compaction, recycle the disk space occupied by the dropped columns, but retain the schema information. To completely drop a column and its metadata and information from the schema, you must run the ALTER TABLE TABLE_NAME FORCE; statement:
- Add or drop a primary key
- Perform hybrid column operations
- Modify the partitioning rule
- Add an auto-increment column
- Truncate a table

For more information about online DDL and offline DDL, 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 the syntax, table_name is the name of the table that contains the column to be dropped; column_name is the name of the column to be dropped.

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

Here, take the C3 column in the tbl1 table as an example to show how to drop a column from a table.

obclient> ALTER TABLE tbl1 DROP COLUMN C3;

After the DESCRIBE tbl1; command is executed again, 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

You can drop multiple columns by using either of the following syntaxes:

-- 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 syntaxes, table_name is the name of the table that contains the columns to be dropped; column_name is the name of the column to be dropped.

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

Here, take the C4 and C5 columns in the tbl1 table as an example to show how to drop multiple columns from a table by using the ALTER TABLE table_name DROP (column_name1, column_name2, ...) method.

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

After the DESCRIBE tbl1; command is executed again, 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  |
+-------+--------------+------+------+---------+-------+

To drop the C2 and C3 columns from the tbl1 table, you can use the ALTER TABLE table_nameE DROP COLUMN column_name1, DROP COLUMN column_name2, ... syntax.

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

After the DESCRIBE tbl1; command is executed again, 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  |
+-------+--------------+------+------+---------+-------+

Purge deprecated columns

The syntax for purging deprecated columns is as follows:

ALTER TABLE TABLE_NAME FORCE;

Here, take the C2, C3, C4, and C5 columns in the tbl1 table as an example to show how to purge deprecated columns from a table.

obclient> ALTER TABLE tbl1 FORCE;

Rename a column

The syntax 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.

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

To rename the C3 column to C4, execute the following statement:

obclient> ALTER TABLE tbl1 RENAME COLUMN C3 TO C4;

Run the DESCRIBE tbl1; statement again to view the structure of the tbl1 table. As shown in the following output, the C3 column has been renamed to C4 in the tbl1 table.

+-------+--------------+------+------+---------+-------+
| 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 types: CHAR and VARCHAR2
Numeric types: NUMBER (precision can be changed only increased)
Character types: CHAR (precision can be changed 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 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 change of a numeric 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 type.

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

Default values of managed columns

If not set, the default value of a column is NULL. The syntax for setting the default value of a managed 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 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 one. For more information, see [Modify the column type](#Modify the column type) above.
const_value: the new default value of the column.

Assume that a table named tbl1 exists in the database and the 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 | 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 to view the structure of the tbl1 table, 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 column constraints to a table, 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 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 a different data type. For more information, see [Modify a column type](#Modify a column type).
NULL | NOT NULL: specifies that the target column can be null (NULL) or cannot be null (NOT NULL).
PRIMARY KEY: specifies that the target column is the primary key.
UNIQUE: specifies that the target column is unique.

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

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

Execute the DESCRIBE tbl1; statement again to view the structure 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 value 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 start from 1 and increase by 1.

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

Manually trigger 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. The C1 column values are 1, 2, 3, and 5. The value 4 is skipped.

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

References

ALTER TABLE