This topic describes how to Rename a table, Rename a column], Change the type of a column, Add a column, Add a primary key, Add a foreign key, Drop a column, Drop a primary key, Use parallel hints in the ALTER TABLE statement, and Modify the partitioning rule of a table in OceanBase Database in Oracle mode.
Prerequisites
The current user has the ALTER TABLE privilege.
Example 1
Create a table named tbl1.
obclient> CREATE TABLE tbl1(col1 NUMBER,col2 VARCHAR2(18),
col3 CHAR(1),col4 VARCHAR2(100),col5 DATE);
Query OK, 0 rows affected
Example 1-1 Rename a table
Rename the tbl1 table as test_tbl1.
obclient> ALTER TABLE tbl1 RENAME test_tbl1;
Query OK, 0 rows affected
Example 1-2 Rename a column
Rename the col2 column in the test_tbl1 table as name.
obclient> ALTER TABLE test_tbl1 RENAME COLUMN col2 TO name;
Query OK, 0 rows affected
obclient> DESC test_tbl1;
+-------+---------------+------+-----+---------+-------+
| FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA |
+-------+---------------+------+-----+---------+-------+
| COL1 | NUMBER | YES | NULL | NULL | NULL |
| NAME | VARCHAR2(18) | YES | NULL | NULL | NULL |
| COL3 | CHAR(1) | YES | NULL | NULL | NULL |
| COL4 | VARCHAR2(100) | YES | NULL | NULL | NULL |
| COL5 | DATE | YES | NULL | NULL | NULL |
+-------+---------------+------+-----+---------+-------+
5 rows in set
Example 1-3 Change the type of a column
Change the data type of the col4 column in the test_tbl1 table from VARCHAR2(100) to CHAR(128).
obclient> ALTER TABLE test_tbl1 MODIFY col4 CHAR(128);
Query OK, 0 rows affected
obclient> DESC test_tbl1;
+-------+--------------+------+-----+---------+-------+
| FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA |
+-------+--------------+------+-----+---------+-------+
| COL1 | NUMBER | YES | NULL | NULL | NULL |
| NAME | VARCHAR2(18) | YES | NULL | NULL | NULL |
| COL3 | CHAR(1) | YES | NULL | NULL | NULL |
| COL4 | CHAR(128) | YES | NULL | NULL | NULL |
| COL5 | DATE | YES | NULL | NULL | NULL |
+-------+--------------+------+-----+---------+-------+
5 rows in set
Example 1-4 Add a column
Add the col6 column to the test_tbl1 table.
obclient> ALTER TABLE test_tbl1 ADD col6 NUMBER;
Query OK, 0 rows affected
obclient> DESC test_tbl1;
+-------+--------------+------+-----+---------+-------+
| FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA |
+-------+--------------+------+-----+---------+-------+
| COL1 | NUMBER | YES | NULL | NULL | NULL |
| NAME | VARCHAR2(18) | YES | NULL | NULL | NULL |
| COL3 | CHAR(1) | YES | NULL | NULL | NULL |
| COL4 | CHAR(128) | YES | NULL | NULL | NULL |
| COL5 | DATE | YES | NULL | NULL | NULL |
| COL6 | NUMBER | YES | NULL | NULL | NULL |
+-------+--------------+------+-----+---------+-------+
6 rows in set
Example 1-5 Add a primary key
Add a primary key for the test_tbl1 table.
obclient> ALTER TABLE test_tbl1 ADD CONSTRAINT pk_test_tbl1 PRIMARY KEY (col1);
Query OK, 0 rows affected
Example 1-6 Add a foreign key
Add a foreign key for the tbl2 table.
obclient> CREATE TABLE tbl2(col1 NUMBER,col2 VARCHAR2(18),col3 NUMBER);
Query OK, 0 rows affected
obclient> ALTER TABLE tbl2 ADD CONSTRAINT fk_tbl2 FOREIGN KEY (col3) REFERENCES test_tbl1(col1);
Query OK, 0 rows affected
Example 1-7 Drop a column
Drop the col6 column from the test_tbl1 table.
obclient> ALTER TABLE test_tbl1 DROP COLUMN col6;
Query OK, 0 rows affected
obclient> DESC test_tbl1;
+-------+--------------+------+-----+---------+-------+
| FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA |
+-------+--------------+------+-----+---------+-------+
| COL1 | NUMBER | NO | PRI | NULL | NULL |
| NAME | VARCHAR2(18) | YES | NULL | NULL | NULL |
| COL3 | CHAR(1) | YES | NULL | NULL | NULL |
| COL4 | CHAR(128) | YES | NULL | NULL | NULL |
| COL5 | DATE | YES | NULL | NULL | NULL |
+-------+--------------+------+-----+---------+-------+
5 rows in set
Example 1-8 Drop a primary key
Note
In Oracle mode, you cannot drop a primary key from its parent table.
Drop the primary key of the test_tbl1 table.
obclient> ALTER TABLE test_tbl1 DROP PRIMARY KEY;
Query OK, 0 rows affected
Example 1-9 Use parallel hints in the ALTER TABLE statement
OceanBase Database allows you to use parallel hints when you perform DDL operations such as changing the data type of a column, dropping a column, and repartitioning a table.
Drop the col5 column from the test_tbl1 table and set the degree of parallelism (DOP) to 10.
obclient> ALTER TABLE /*+ PARALLEL(10) */ test_tbl1 DROP COLUMN col5;
Query OK, 0 rows affected
Example 2 Modify the partitioning rule of a table
In OceanBase Database in Oracle mode:
You can convert a non-partitioned table into a partitioned or subpartitioned table, regardless of whether the subpartitioned table is created based on a template.
Example 2-1 Convert a non-partitioned table into a partitioned table
Create a non-partitioned table named tbl3 and convert the tbl1 table into a HASH-partitioned table.
obclient> CREATE TABLE tbl3(col1 NUMBER,col2 NUMBER);
Query OK, 0 rows affected
obclient> ALTER TABLE tbl3 MODIFY PARTITION BY HASH(col1) PARTITIONS 5;
Query OK, 0 rows affected
Example 2-2 Convert a non-partitioned table into a subpartitioned table
Create a non-partitioned table named tbl4 and convert the tbl4 table into a template-based HASH-RANGE-subpartitioned table.
obclient> CREATE TABLE tbl4(col1 NUMBER,col2 NUMBER);
Query OK, 0 rows affected
obclient> ALTER TABLE tbl4 MODIFY PARTITION BY HASH(col1)
SUBPARTITION BY RANGE(col2)
SUBPARTITION TEMPLATE(
SUBPARTITION sp1 VALUES LESS THAN (2020),
SUBPARTITION sp2 VALUES LESS THAN (2024))
PARTITIONS 5;
Query OK, 0 rows affected