This topic describes ALTER TABLE DDL operations that are executed in an Oracle database and can be converted by the DDL synchronization component of the data transmission service but cannot be executed in the Oracle compatible mode of OceanBase Database during data migration from the Oracle database to the Oracle compatible mode of OceanBase Database.
Change the type of a column
Oracle databases and the Oracle compatible mode of OceanBase Database have different limitations on the target column types for changing the type of a constrained column. For more information, see Change the type of a constrained column.
Oracle databases and the Oracle compatible mode of OceanBase Database have different limitations on the target column types for changing the type of an unconstrained column. For more information, see Change the type of an unconstrained column.
Change the length of a column
Oracle databases and the Oracle compatible mode of OceanBase Database have different limitations on the target column types for changing the length of a constrained column. For more information, see Change the length of a constrained column.
Oracle databases and the Oracle compatible mode of OceanBase Database have different limitations on the target column types for changing the length of an unconstrained column. For more information, see Change the length of an unconstrained column.
Automatically create subpartitions under a new partition based on an existing subpartition template
Support for the following operation is different between an Oracle database and the Oracle compatible mode of OceanBase Database when the original table structure contains a subpartition template:
Oracle database: supported.
Oracle compatible mode of OceanBase Database: does not support automatically creating subpartitions.
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'));
Specify custom subpartitions in the DDL statement for creating a new partition when a subpartition template exists
Support for the following operation is different between an Oracle database and the Oracle compatible mode of OceanBase Database when the original table structure contains a subpartition template:
Oracle database: supported.
Oracle compatible mode of OceanBase Database: not supported.
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));