DDL synchronization scope

2025-10-09 03:34:24  Updated

This topic describes the DDL statements that are related to tables and indexes and are supported by different types of data migration or synchronization tasks.

Considerations

  • The DDL synchronization information provided in this topic is limited by the complexity of DDL statements and does not cover all scenarios, such as all options of the ALTER TABLE ADD COLUMN statement. If a supported option cannot be used, contact Alibaba Cloud Technical Support or refer to the relevant topic on the product page.

  • The DDL statements provided in this topic are examples. The syntax of a DDL statement may vary depending on database support.

  • For the CREATE DDL operations performed on tables in the DDL synchronization information in this topic, select the affected objects by using Match by Rule. The name of a table that is created by using the CREATE statement must be included in the Object Migration Rules field of the matching rules so that the related DDL statements can be synchronized to the target database.

  • For the RENAME DDL operations performed on tables in the DDL synchronization information in this topic, select the affected objects by using Match by Rule. The name of a table after the RENAME statement is executed must be included in the Object Migration Rules field of the matching rules so that the related DDL statements can be synchronized to the target database.

  • For the index-related DDL operations in the DDL synchronization information in this topic, the table objects on which the indexes are located must be included in the Object Migration Rules field so that the related DDL statements can be synchronized to the target database.

  • For reverse incremental DDL operations in data migration tasks, see the "Target database > Source database" support information in the following table. For example, if the source database is a MySQL database and the target database is the MySQL compatible mode of OceanBase Database, see the support information for synchronizing DDL statements from a MySQL database to the MySQL compatible mode of OceanBase Database.

MySQL -> OB_MySQL

The following table describes DDL synchronization when you migrate data from a MySQL database to the MySQL compatible mode of OceanBase Database. For more information, see DDL synchronization from a MySQL database to the MySQL compatible mode of OceanBase Database.

Object type DDL type DDL statement Supported?
Table CREATE CREATE TABLE A Yes
Table CREATE CREATE TABLE A LIKE B Yes
Table CREATE CREATE TABLE IF NOT EXISTS A Yes
Table CREATE CREATE TABLE AS SELECT No
Table ALTER ALTER TABLE A RENAME B Yes
Table ALTER ALTER TABLE A ADD COLUMN B Yes
Table ALTER ALTER TABLE A DROP COLUMN B Yes
Table ALTER ALTER TABLE A ALTER COLUMN SET DEFAULT Yes
Table ALTER ALTER TABLE A ALTER COLUMN DROP DEFAULT Yes
Table ALTER ALTER TABLE A CHANGE COLUMN Yes
Table ALTER ALTER TABLE A MODIFY COLUMN Yes
Table ALTER ALTER TABLE A ADD INDEX/KEY B Yes
Table ALTER ALTER TABLE A DROP INDEX/KEY B Yes
Table ALTER ALTER TABLE A ADD FULLTEXT INDEX/KEY B No
Table ALTER ALTER TABLE A ADD UNIQUE INDEX/KEY B Yes
Table ALTER ALTER TABLE A ADD CONSTRAINT B Yes
Table ALTER ALTER TABLE A DROP CONSTRAINT B No
Table ALTER ALTER TABLE A DROP PRIMARY KEY Yes

Note

OceanBase Database V4.0.0 and later support this operation only on the target side.
Table ALTER ALTER TABLE A ADD PARTITION B Yes
Table ALTER ALTER TABLE A DROP PARTITION B Yes
Table ALTER ALTER TABLE A TRUNCATE PARTITION B Yes
Table ALTER ALTER TABLE A PARTITION BY... Yes
Table DROP DROP TABLE A Yes
Table RENAME RENAME TABLE A TO B Yes
Table TRUNCATE TRUNCATE TABLE A Yes
Index CREATE CREATE INDEX A ON B(C) Yes
Index CREATE CREATE UNIQUE INDEX A ON B(C) Yes
Index DROP DROP INDEX A ON B Yes

OB_MySQL -> MySQL

The following table describes DDL synchronization from OceanBase Database in the MySQL compatible mode to MySQL Database. For more information, see DDL synchronization from OceanBase Database in the MySQL compatible mode to MySQL Database.

Object type DDL type DDL statement Supported?
Table CREATE CREATE TABLE A Yes
Table CREATE CREATE TABLE A LIKE B Yes
Table CREATE CREATE TABLE IF NOT EXISTS A Yes
Table CREATE CREATE TABLE AS SELECT No
Table ALTER ALTER TABLE A RENAME B Yes
Table ALTER ALTER TABLE A ADD COLUMN B Yes
Table ALTER ALTER TABLE A DROP COLUMN B Yes
Table ALTER ALTER TABLE A ALTER COLUMN SET DEFAULT Yes
Table ALTER ALTER TABLE A ALTER COLUMN DROP DEFAULT Yes
Table ALTER ALTER TABLE A CHANGE COLUMN Yes
Table ALTER ALTER TABLE A MODIFY COLUMN Yes
Table ALTER ALTER TABLE A ADD INDEX/KEY B Yes
Table ALTER ALTER TABLE A DROP INDEX/KEY B Yes
Table ALTER ALTER TABLE A ADD FULLTEXT INDEX/KEY B No
Table ALTER ALTER TABLE A ADD UNIQUE INDEX/KEY B Yes
Table ALTER ALTER TABLE A ADD CONSTRAINT B Yes
Table ALTER ALTER TABLE A DROP CONSTRAINT B No
Table ALTER ALTER TABLE A DROP PRIMARY KEY Yes

Note

Only supported in OceanBase Database V4.0.0 and later versions.
Table ALTER ALTER TABLE A ADD PARTITION B Yes
Table ALTER ALTER TABLE A DROP PARTITION B Yes
Table ALTER ALTER TABLE A TRUNCATE PARTITION B Yes
Table ALTER ALTER TABLE A PARTITION BY... Yes
Table DROP DROP TABLE A Yes
Table RENAME RENAME TABLE A TO B Yes
Table TRUNCATE TRUNCATE TABLE A Yes
Index CREATE CREATE INDEX A ON B(C) Yes
Index CREATE CREATE UNIQUE INDEX A ON B(C) Yes
Index DROP DROP INDEX A ON B Yes

OB_MySQL -> OB_MySQL

The following table describes DDL synchronization during data migration between MySQL-compatible tenants of OceanBase Database. For more information, see DDL synchronization between MySQL-compatible tenants of OceanBase Database.

Object type DDL type DDL statement Supported?
Table CREATE CREATE TABLE A Yes
Table CREATE CREATE TABLE A LIKE B Yes
Table CREATE CREATE TABLE IF NOT EXISTS A Yes
Table CREATE CREATE TABLE AS SELECT No
Table ALTER ALTER TABLE A RENAME B Yes
Table ALTER ALTER TABLE A ADD COLUMN B Yes
Table ALTER ALTER TABLE A DROP COLUMN B Yes
Table ALTER ALTER TABLE A ALTER COLUMN SET DEFAULT Yes
Table ALTER ALTER TABLE A ALTER COLUMN DROP DEFAULT Yes
Table ALTER ALTER TABLE A CHANGE COLUMN Yes
Table ALTER ALTER TABLE A MODIFY COLUMN Yes
Table ALTER ALTER TABLE A ADD INDEX/KEY B Yes
Table ALTER ALTER TABLE A DROP INDEX/KEY B Yes
Table ALTER ALTER TABLE A ADD FULLTEXT INDEX/KEY B No
Table ALTER ALTER TABLE A ADD UNIQUE INDEX/KEY B Yes
Table ALTER ALTER TABLE A ADD CONSTRAINT B Yes
Table ALTER ALTER TABLE A DROP CONSTRAINT B No
Table ALTER ALTER TABLE A DROP PRIMARY KEY Yes

Note

Only supported in OceanBase Database V4.0.0 and later versions at both source and target.
Table ALTER ALTER TABLE A ADD PARTITION B Yes
Table ALTER ALTER TABLE A DROP PARTITION B Yes
Table ALTER ALTER TABLE A TRUNCATE PARTITION B Yes
Table ALTER ALTER TABLE A PARTITION BY... Yes
Table DROP DROP TABLE A Yes
Table RENAME RENAME TABLE A TO B Yes
Table TRUNCATE TRUNCATE TABLE A Yes
Index CREATE CREATE INDEX A ON B(C) Yes
Index CREATE CREATE UNIQUE INDEX A ON B(C) Yes
Index DROP DROP INDEX A ON B Yes

Oracle -> OB_Oracle

This topic describes DDL synchronization during data migration from an Oracle database to the Oracle compatible mode of OceanBase Database. For more information, see DDL synchronization from Oracle to OceanBase Database Oracle compatible mode.

  • If Obtain Incremental Data through Kafka is not selected for the Oracle data source:

    Object type DDL type DDL statement Supported?
    Table CREATE CREATE TABLE A Yes
    Table CREATE CREATE TABLE A LIKE B N/A
    Table CREATE CREATE TABLE IF NOT EXISTS A N/A
    Table CREATE CREATE TABLE AS SELECT No
    Table ALTER ALTER TABLE A RENAME B N/A
    Table ALTER ALTER TABLE A ADD COLUMN B Yes
    Table ALTER ALTER TABLE A DROP COLUMN B Yes
    Table ALTER ALTER TABLE A ALTER COLUMN SET DEFAULT Yes
    Table ALTER ALTER TABLE A ALTER COLUMN DROP DEFAULT Yes
    Table ALTER ALTER TABLE A CHANGE COLUMN Yes
    Table ALTER ALTER TABLE A MODIFY COLUMN Yes
    Table ALTER ALTER TABLE A ADD INDEX/KEY B N/A
    Table ALTER ALTER TABLE A DROP INDEX/KEY B N/A
    Table ALTER ALTER TABLE A ADD FULLTEXT INDEX/KEY B N/A
    Table ALTER ALTER TABLE A ADD UNIQUE INDEX/KEY B N/A
    Table ALTER ALTER TABLE A ADD CONSTRAINT B Yes
    Table ALTER ALTER TABLE A DROP CONSTRAINT B Yes
    Table ALTER ALTER TABLE A DROP PRIMARY KEY Yes

    Note

    OceanBase Database V4.0.0 and later support this operation only.
    Table ALTER ALTER TABLE A ADD PARTITION B Yes
    Table ALTER ALTER TABLE A DROP PARTITION B Yes
    Table ALTER ALTER TABLE A TRUNCATE PARTITION B Yes
    Table ALTER ALTER TABLE A PARTITION BY... N/A
    Table DROP DROP TABLE A Yes
    Table RENAME RENAME TABLE A TO B Yes
    Table TRUNCATE TRUNCATE TABLE A Yes
    Index CREATE CREATE INDEX A ON B(C) Yes
    Index CREATE CREATE UNIQUE INDEX A ON B(C) Yes
    Index DROP DROP INDEX A ON B Yes

  • If Obtain incremental data through Kafka is selected for the Oracle data source:

    Object type DDL type DDL statement Supported?
    Table TRUNCATE TRUNCATE TABLE A Yes

OB_Oracle -> Oracle

The following table describes DDL synchronization from OceanBase Database in the Oracle compatible mode to Oracle Database. For more information, see DDL synchronization from OceanBase Database in the Oracle compatible mode to Oracle Database.

Object type DDL type DDL statement Supported?
Table CREATE CREATE TABLE A Yes
Table CREATE CREATE TABLE A LIKE B N/A
Table CREATE CREATE TABLE IF NOT EXISTS A N/A
Table CREATE CREATE TABLE AS SELECT No
Table ALTER ALTER TABLE A RENAME B Yes
Table ALTER ALTER TABLE A ADD COLUMN B Yes
Table ALTER ALTER TABLE A DROP COLUMN B Yes
Table ALTER ALTER TABLE A ALTER COLUMN SET DEFAULT Yes
Table ALTER ALTER TABLE A ALTER COLUMN DROP DEFAULT Yes
Table ALTER ALTER TABLE A CHANGE COLUMN Yes
Table ALTER ALTER TABLE A MODIFY COLUMN Yes
Table ALTER ALTER TABLE A ADD INDEX/KEY B N/A
Table ALTER ALTER TABLE A DROP INDEX/KEY B N/A
Table ALTER ALTER TABLE A ADD FULLTEXT INDEX/KEY B N/A
Table ALTER ALTER TABLE A ADD UNIQUE INDEX/KEY B N/A
Table ALTER ALTER TABLE A ADD CONSTRAINT B Yes
Table ALTER ALTER TABLE A DROP CONSTRAINT B Yes
Table ALTER ALTER TABLE A DROP PRIMARY KEY Yes

Note

Only supported in OceanBase Database V4.0.0 and later versions.
Table ALTER ALTER TABLE A ADD PARTITION B Yes
Table ALTER ALTER TABLE A DROP PARTITION B Yes
Table ALTER ALTER TABLE A TRUNCATE PARTITION B Yes
Table ALTER ALTER TABLE A PARTITION BY... N/A
Table DROP DROP TABLE A Yes
Table RENAME RENAME TABLE A TO B Yes
Table TRUNCATE TRUNCATE TABLE A Yes
Index CREATE CREATE INDEX A ON B(C) Yes
Index CREATE CREATE UNIQUE INDEX A ON B(C) Yes
Index DROP DROP INDEX A ON B Yes

OB_Oracle -> OB_Oracle

This topic describes DDL synchronization between OceanBase Database's Oracle-compatible tenants. For more information, see DDL synchronization between Oracle-compatible tenants of OceanBase Database.

Object type DDL type DDL statement Supported?
Table CREATE CREATE TABLE A Yes
Table CREATE CREATE TABLE A LIKE B Yes
Table CREATE CREATE TABLE IF NOT EXISTS A Yes
Table CREATE CREATE TABLE AS SELECT No
Table ALTER ALTER TABLE A RENAME B Yes
Table ALTER ALTER TABLE A ADD COLUMN B Yes
Table ALTER ALTER TABLE A DROP COLUMN B Yes
Table ALTER ALTER TABLE A ALTER COLUMN SET DEFAULT Yes
Table ALTER ALTER TABLE A ALTER COLUMN DROP DEFAULT Yes
Table ALTER ALTER TABLE A CHANGE COLUMN Yes
Table ALTER ALTER TABLE A MODIFY COLUMN Yes
Table ALTER ALTER TABLE A ADD INDEX/KEY B N/A
Table ALTER ALTER TABLE A DROP INDEX/KEY B N/A
Table ALTER ALTER TABLE A ADD FULLTEXT INDEX/KEY B N/A
Table ALTER ALTER TABLE A ADD UNIQUE INDEX/KEY B N/A
Table ALTER ALTER TABLE A ADD CONSTRAINT B Yes
Table ALTER ALTER TABLE A DROP CONSTRAINT B Yes
Table ALTER ALTER TABLE A DROP PRIMARY KEY Yes

Note

Only supported in OceanBase Database V4.0.0 and later versions at both source and target.
Table ALTER ALTER TABLE A ADD PARTITION B Yes
Table ALTER ALTER TABLE A DROP PARTITION B Yes
Table ALTER ALTER TABLE A TRUNCATE PARTITION B Yes
Table ALTER ALTER TABLE A PARTITION BY... N/A
Table DROP DROP TABLE A Yes
Table RENAME RENAME TABLE A TO B Yes
Table TRUNCATE TRUNCATE TABLE A Yes
Index CREATE CREATE INDEX A ON B(C) Yes
Index CREATE CREATE UNIQUE INDEX A ON B(C) Yes
Index DROP DROP INDEX A ON B Yes

PostgreSQL -> OB_MySQL

The following table describes DDL synchronization when you migrate data from a PostgreSQL database to a MySQL-compatible tenant of OceanBase Database. For more information, see Synchronize DDL operations from a PostgreSQL database to a MySQL-compatible tenant of OceanBase Database.

Object type DDL type DDL statement Supported
Table CREATE CREATE TABLE A Yes
Table CREATE CREATE TABLE IF NOT EXISTS A Yes
Table ALTER ALTER TABLE A ALTER COLUMN B TYPE new_type Yes
Table ALTER ALTER TABLE A RENAME COLUMN Yes
Table ALTER ALTER TABLE A ALTER COLUMN SET DEFAULT Yes
Table ALTER ALTER TABLE A ALTER COLUMN DROP DEFAULT Yes
Table ALTER ALTER TABLE A RENAME Yes
Table ALTER ALTER TABLE A ADD COLUMN B Yes
Table ALTER ALTER TABLE A ADD COLUMN B GENERATED ALWAYS AS (expression) STORED Yes
Table ALTER ALTER TABLE A DROP COLUMN B Yes
Table ALTER ALTER TABLE A ADD PRIMARY KEY Yes
Table DROP DROP TABLE A Yes
Table TRUNCATE TRUNCATE TABLE A Yes
Index CREATE CREATE INDEX A ON B(C) Yes
Index CREATE CREATE UNIQUE INDEX A ON B(C) Yes

PostgreSQL -> OB_Oracle

The following table describes DDL synchronization when you migrate data from a PostgreSQL database to an Oracle-compatible tenant of OceanBase Database. For more information, see Synchronize DDL operations from a PostgreSQL database to an Oracle-compatible tenant of OceanBase Database.

Object type DDL type DDL statement Supported
Table CREATE CREATE TABLE A Yes
Table CREATE CREATE TABLE IF NOT EXISTS A Yes
Table ALTER ALTER TABLE A ALTER COLUMN B TYPE new_type Yes
Table ALTER ALTER TABLE A RENAME COLUMN Yes
Table ALTER ALTER TABLE A ALTER COLUMN SET DEFAULT Yes
Table ALTER ALTER TABLE A ALTER COLUMN DROP DEFAULT Yes
Table ALTER ALTER TABLE A RENAME Yes
Table ALTER ALTER TABLE A ADD COLUMN B Yes
Table ALTER ALTER TABLE A ADD COLUMN B GENERATED ALWAYS AS (expression) STORED Yes
Table ALTER ALTER TABLE A DROP COLUMN B Yes
Table ALTER ALTER TABLE A ADD PRIMARY KEY Yes
Table ALTER ALTER TABLE A DROP CONSTRAINT B Yes
Table DROP DROP TABLE A Yes
Table TRUNCATE TRUNCATE TABLE A Yes
Index CREATE CREATE INDEX A ON B(C) Yes
Index CREATE CREATE UNIQUE INDEX A ON B(C) Yes

OB_MySQL -> DataHub (BLOB)

The following table describes DDL synchronization when you synchronize data from a MySQL-compatible tenant of OceanBase Database to a DataHub instance of the BLOB data type.

Object type DDL type DDL statement Supported?
Table ALTER ALTER TABLE A ADD COLUMN B Yes
Table ALTER ALTER TABLE A DROP COLUMN B Yes
Table ALTER ALTER TABLE A MODIFY COLUMN B Yes
Table TRUNCATE TRUNCATE TABLE A

Note

In delayed deletion, the same transaction contains two identical TRUNCATE TABLE DDL statements. In this case, idempotence is implemented for downstream consumption.

 Yes
Index CREATE CREATE INDEX A ON B(C) Yes
Index CREATE CREATE UNIQUE INDEX A ON B(C) Yes
Index DROP DROP INDEX A ON B Yes

OB_Oracle -> DataHub (BLOB)

The following table describes DDL synchronization when you synchronize data from OceanBase Database in the Oracle compatible mode to a DataHub instance of the BLOB data type.

Object type DDL type DDL statement Supported?
Table ALTER ALTER TABLE A ADD COLUMN B Yes
Table ALTER ALTER TABLE A DROP COLUMN B Yes
Table ALTER ALTER TABLE A MODIFY COLUMN B Yes
Table TRUNCATE TRUNCATE TABLE A

Note

In delayed deletion, the same transaction contains two identical TRUNCATE TABLE DDL statements. In this case, idempotence is implemented for downstream consumption.

 Yes
Index CREATE CREATE INDEX A ON B(C) Yes
Index CREATE CREATE UNIQUE INDEX A ON B(C) Yes
Index DROP DROP INDEX A ON B Yes

OB_MySQL -> Kafka

The following table describes DDL synchronization when you synchronize data from the MySQL compatible mode of OceanBase Database to a Kafka data source.

Object type DDL type DDL statement Supported?
Table CREATE CREATE TABLE A Yes
Table CREATE CREATE TABLE A LIKE B Yes
Table CREATE CREATE TABLE IF NOT EXISTS A Yes
Table CREATE CREATE TABLE AS SELECT No
Table ALTER ALTER TABLE A RENAME B Yes
Table ALTER ALTER TABLE A ADD COLUMN B Yes
Table ALTER ALTER TABLE A DROP COLUMN B Yes
Table ALTER ALTER TABLE A ALTER COLUMN SET DEFAULT Yes
Table ALTER ALTER TABLE A ALTER COLUMN DROP DEFAULT Yes
Table ALTER ALTER TABLE A CHANGE COLUMN Yes
Table ALTER ALTER TABLE A MODIFY COLUMN Yes
Table ALTER ALTER TABLE A ADD INDEX/KEY B Yes
Table ALTER ALTER TABLE A DROP INDEX/KEY B Yes
Table ALTER ALTER TABLE A ADD FULLTEXT INDEX/KEY B No
Table ALTER ALTER TABLE A ADD UNIQUE INDEX/KEY B Yes
Table ALTER ALTER TABLE A ADD CONSTRAINT B Yes
Table ALTER ALTER TABLE A DROP CONSTRAINT B No
Table ALTER ALTER TABLE A DROP PRIMARY KEY Yes

Note

Only supported in OceanBase Database V4.0.0 and later versions for source and target databases.
Table ALTER ALTER TABLE A ADD PARTITION B Yes
Table ALTER ALTER TABLE A DROP PARTITION B Yes
Table ALTER ALTER TABLE A TRUNCATE PARTITION B Yes
Table ALTER ALTER TABLE A PARTITION BY... Yes
Table DROP DROP TABLE A Yes
Table RENAME RENAME TABLE A TO B Yes
Table TRUNCATE TRUNCATE TABLE A Yes
Index CREATE CREATE INDEX A ON B(C) Yes
Index CREATE CREATE UNIQUE INDEX A ON B(C) Yes
Index DROP DROP INDEX A ON B Yes

OB_Oracle -> Kafka

The following table describes DDL synchronization when you synchronize data from the Oracle compatible mode of OceanBase Database to a Kafka data source.

Object type DDL type DDL statement Supported?
Table CREATE CREATE TABLE A Yes
Table CREATE CREATE TABLE A LIKE B Yes
Table CREATE CREATE TABLE IF NOT EXISTS A Yes
Table CREATE CREATE TABLE AS SELECT No
Table ALTER ALTER TABLE A RENAME B Yes
Table ALTER ALTER TABLE A ADD COLUMN B Yes
Table ALTER ALTER TABLE A DROP COLUMN B Yes
Table ALTER ALTER TABLE A ALTER COLUMN SET DEFAULT Yes
Table ALTER ALTER TABLE A ALTER COLUMN DROP DEFAULT Yes
Table ALTER ALTER TABLE A CHANGE COLUMN Yes
Table ALTER ALTER TABLE A MODIFY COLUMN Yes
Table ALTER ALTER TABLE A ADD INDEX/KEY B N/A
Table ALTER ALTER TABLE A DROP INDEX/KEY B N/A
Table ALTER ALTER TABLE A ADD FULLTEXT INDEX/KEY B N/A
Table ALTER ALTER TABLE A ADD UNIQUE INDEX/KEY B N/A
Table ALTER ALTER TABLE A ADD CONSTRAINT B Yes
Table ALTER ALTER TABLE A DROP CONSTRAINT B Yes
Table ALTER ALTER TABLE A DROP PRIMARY KEY Yes

Note

Only supported in OceanBase Database V4.0.0 and later versions at both the source and target.
Table ALTER ALTER TABLE A ADD PARTITION B Yes
Table ALTER ALTER TABLE A DROP PARTITION B Yes
Table ALTER ALTER TABLE A TRUNCATE PARTITION B Yes
Table ALTER ALTER TABLE A PARTITION BY... N/A
Table DROP DROP TABLE A Yes
Table RENAME RENAME TABLE A TO B Yes
Table TRUNCATE TRUNCATE TABLE A Yes
Index CREATE CREATE INDEX A ON B(C) Yes
Index CREATE CREATE UNIQUE INDEX A ON B(C) Yes
Index DROP DROP INDEX A ON B Yes
Last
Next

OceanBaseOceanBase

OceanBase, A Highly Scalable Database for Transactional, Analytical, and AI Workloads.

Company

About OceanBaseContact UsPartnerTrust Center

Product

OceanBase CloudOceanBase EnterpriseQuick StartDownloadPricing

Resources

DocsBlogWhite Paper

Follow us

LinkedInLinkedInDiscordDiscordTwitterTwitterYoutubeYoutubeForumForumGitHubGitHubStack OverflowStack Overflow

© OceanBase 2024. All rights reserved | Cloud Service Agreement | Privacy Policy | Legal | Security

Contact Us