Incompatibilities of ALTER TABLE - ApsaraDB for OceanBase

This topic describes the scenarios where ALTER TABLE DDL operations performed on an Oracle database can be converted by the DDL synchronization component of the data transmission service but cannot be performed in an Oracle tenant of OceanBase Database during data migration from the Oracle database to the Oracle tenant of OceanBase Database.

Change the type of a column

Oracle databases and Oracle tenants of OceanBase Database have different limitations on the target column types for changing the types of constrained columns. For more information, see Change the type of a constrained column.
Oracle databases and Oracle tenants of OceanBase Database have different limitations on the target column types for changing the types of unconstrained columns. For more information, see Change the type of an unconstrained column.

Change the length of a column

Oracle databases and Oracle tenants of OceanBase Database have different limitations on the target column types for changing the types of constrained columns. For more information, see Change the length of a constrained column.
Oracle databases and Oracle tenants of OceanBase Database have different limitations on the target column types for changing the types of unconstrained columns. For more information, see Change the length of an unconstrained column.

Automatically create subpartitions under a new partition

Take note of the following items on the support for automatically creating subpartitions, based on an existing subpartition template in the original schema, under a new partition created by using the ALTER TABLE DDL statement:

Supported in an Oracle database.
Not supported in an Oracle tenant of OceanBase Database. Subpartitions cannot be automatically created.

Here is an example:

CREATE TABLE SMNOTIFY2
   (	"REGION" NUMBER(4,0) NOT NULL ENABLE, 
"INTIME" DATE DEFAULT sysdate NOT NULL ENABLE
   ) 
  PARTITION BY RANGE ("INTIME") 
  SUBPARTITION BY LIST ("REGION") 
SUBPARTITION TEMPLATE ( 
    SUBPARTITION "L_23" VALUES ( 23 ),
	SUBPARTITION "L_24" VALUES ( 24 ))
 (PARTITION "P_R_202208"  VALUES LESS THAN (TO_DATE(' 2022-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
 PARTITION "P_R_202209"  VALUES LESS THAN (TO_DATE('2022-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
 PARTITION "P_R_PMAX"  VALUES LESS THAN (TO_DATE('2038-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')));
 
 ALTER TABLE SMNOTIFY2 ADD PARTITION new_partition1 VALUES LESS THAN(TO_DATE('2043-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN'));

Create user-defined subpartitions under a new partition

Take note of the following items on the support for creating user-defined subpartitions, given an existing subpartition template in the original schema, under a new partition created by using the ALTER TABLE DDL statement:

Supported in an Oracle database. The subpartition template is ignored. Only user-defined subpartitions are created.
Not supported in an Oracle tenant of OceanBase Database.

Here is an example:

CREATE TABLE SMNOTIFY3
   (	"REGION" NUMBER(4,0) NOT NULL ENABLE, 
"INTIME" DATE DEFAULT sysdate NOT NULL ENABLE
   ) 
  PARTITION BY RANGE ("INTIME") 
  SUBPARTITION BY LIST ("REGION") 
SUBPARTITION TEMPLATE ( 
    SUBPARTITION "L_23" VALUES ( 23 ))
 (PARTITION "P_R_202208"  VALUES LESS THAN (TO_DATE(' 2022-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
 PARTITION "P_R_202209"  VALUES LESS THAN (TO_DATE('2022-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
 PARTITION "P_R_PMAX"  VALUES LESS THAN (TO_DATE('2038-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')));
 
 ALTER TABLE SMNOTIFY3 ADD PARTITION new_partition2 VALUES LESS THAN(TO_DATE('2044-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))(SUBPARTITION sp6 VALUES (30));