数据迁移服务隐藏列机制说明

背景信息

如果您在运行数据迁移任务时，没有进入正向切换步骤，请根据本文不同数据迁移任务的操作，自行删除隐藏列和唯一索引。

如果您在运行数据迁移任务时，进入了正向切换步骤，数据传输会根据数据迁移任务的类型，自动删除隐藏列和唯一索引：

• 迁移 Oracle 数据库的数据至 OceanBase 数据库 Oracle 租户时，数据传输将在目标端为全部表对象增加隐藏列，并基于隐藏列创建唯一索引，对应的列和索引将在正向切换步骤被自动移除。创建任务的详情请参见 迁移 Oracle 数据库的数据至 OceanBase 数据库 Oracle 租户。

  数据传输会自动添加隐藏列至 CREATE TABLE 语句中，如下所示：

  CREATE TABLE "table1" (
  	"S1" NVARCHAR2(2) NOT NULL,
  	"S2" NVARCHAR2(20) DEFAULT '默认值字段',
  	"OMS_OBJECT_NUMBER" NUMBER INVISIBLE,
    "OMS_RELATIVE_FNO" NUMBER INVISIBLE,
    "OMS_BLOCK_NUMBER" NUMBER INVISIBLE,
    "OMS_ROW_NUMBER" NUMBER INVISIBLE
  );

• 下述表格的数据迁移任务为了支持无唯一键表迁移，数据传输将在目标端为对应表添加隐藏列，并基于隐藏列创建唯一索引，对应的列和索引将在正向切换步骤被自动移除。

  源端	目标端	参考文档
  OceanBase 数据库 MySQL 租户	OceanBase 数据库 MySQL 租户	OceanBase 数据库之间的数据迁移
  OceanBase 数据库 Oracle 租户	OceanBase 数据库 Oracle 租户	OceanBase 数据库之间的数据迁移
  OceanBase 数据库 Oracle 租户	Oracle 数据库	迁移 OceanBase 数据库 Oracle 租户的数据至 Oracle 数据库

  • OceanBase 数据库 MySQL 租户之间的数据迁移，数据传输会自动添加隐藏列、隐藏列注释和唯一索引至 CREATE TABLE 语句中。

    //没有分区字段，创建隐藏列和唯一索引
    create table `table2` (
    	`c1` binary(1),
    	`OMS_PK_INCRMT` bigint COMMENT 'Reserved for data migration tasks of OMS',
    	UNIQUE KEY `UK_table2_OBPK_INCRMT` (`OMS_PK_INCRMT`)
    );
    //有分区字段，创建隐藏列和唯一索引
    create table `partlistcolumns_key_nopri` (
        `col1` int(11),
        `col2` decimal(10,2),
        `col3` decimal(10,2),
        `col4` bit(1),
        `col5` tinyint(4),
        `col6` smallint(6),
        `OMS_PK_INCRMT` bigint COMMENT 'Reserved for data migration tasks of OMS',
        UNIQUE KEY `UK_partlistcolumns_key_nopri_OBPK_INCRMT` (`col1`,`col6`, `OMS_PK_INCRMT`) LOCAL
    )
    default charset=utf8mb4
    default collate=utf8mb4_general_ci
    PARTITION BY LIST COLUMNS (col1)
    SUBPARTITION BY KEY (col6)
    subpartition template (
        SUBPARTITION `p0`,
        SUBPARTITION `p1`,
        SUBPARTITION `p2`,
        SUBPARTITION `p3`
    )
    (
        PARTITION `p1` VALUES IN (1),
        PARTITION `p2` VALUES IN (2),
        PARTITION `p3` VALUES IN (3),
        PARTITION `p4` VALUES IN (4)
    );

  • OceanBase 数据库 Oracle 租户之间的数据迁移，数据传输会自动添加隐藏列至 CREATE TABLE 语句中。

    CREATE TABLE "table4" (
    	"V1" CHAR(20 BYTE) DEFAULT 'test',
    	"OMS_PK_INCRMT" NUMBER INVISIBLE
    );

  • OceanBase 数据库 Oracle 租户至 Oracle 数据库的数据迁移，数据传输会自动添加隐藏列至 CREATE TABLE 语句中。

    //Oracle 数据库 12C 之前版本
    CREATE TABLE "table3" (
    	"id" NUMBER,
    	"status" VARCHAR2(1 BYTE),
    	"OMS_PK_INCRMT" NUMBER
    );
    //Oracle 数据库 12C 及之后版本
    CREATE TABLE "table3" (
    	"id" NUMBER,
    	"status" VARCHAR2(1 BYTE),
    	"OMS_PK_INCRMT" NUMBER INVISIBLE
    );

前置说明

• "{partition_col_0}" 或者 `{partition_col_0}` 为表分区字段，可能存在多个。存在多个时，请使用英文逗号（,）分隔。例如，"{partition_col_0}", "{partition_col_1}","{partition_col_2}" 或 `{partition_col_0}`,`{partition_col_1}`,`{partition_col_2}`。

• {schema}、{table}、{partition_col_0}、{partition_col_1} 分别为库名、表名、分区字段名、分区字段名的占位符。

Oracle 数据库至 OceanBase 数据库 Oracle 租户的数据迁移

迁移 Oracle 数据库的数据至 OceanBase 数据库 Oracle 租户时，数据传输不区分是否为无主键表，会统一添加隐藏列和唯一索引。

• 添加隐藏列

  ALTER TABLE "{schema}"."{table}" ADD "OMS_OBJECT_NUMBER" NUMBER INVISIBLE;
  ALTER TABLE "{schema}"."{table}" ADD "OMS_RELATIVE_FNO" NUMBER INVISIBLE;
  ALTER TABLE "{schema}"."{table}" ADD "OMS_BLOCK_NUMBER" NUMBER INVISIBLE;
  ALTER TABLE "{schema}"."{table}" ADD "OMS_ROW_NUMBER" NUMBER INVISIBLE;

• 添加唯一索引

  // 有分区字段
  CREATE UNIQUE INDEX "{schema}"."UK_{table}_OMS_ROWID" ON "{schema}"."{table}" ("OMS_OBJECT_NUMBER", "OMS_RELATIVE_FNO", "OMS_BLOCK_NUMBER", "OMS_ROW_NUMBER", "{partition_col_0}", "{partition_col_1}") LOCAL;
  // 没有分区字段
  CREATE UNIQUE INDEX "{schema}"."UK_{table}_OMS_ROWID" ON "{schema}"."{table}" ("OMS_OBJECT_NUMBER", "OMS_RELATIVE_FNO", "OMS_BLOCK_NUMBER", "OMS_ROW_NUMBER");

• 删除唯一索引

  DROP INDEX "{schema}"."UK_{table}_OMS_ROWID";

• 删除隐藏列

  ALTER TABLE "{schema}"."{table}" DROP ("OMS_OBJECT_NUMBER", "OMS_RELATIVE_FNO", "OMS_BLOCK_NUMBER", "OMS_ROW_NUMBER");

OceanBase 数据库 MySQL 租户之间的数据迁移

迁移 OceanBase 数据库 MySQL 租户的数据至 OceanBase 数据库 MySQL 租户时，数据传输会对无唯一键表添加列和唯一索引。

您可以执行下述命令（其中 {schema}、{table} 需要替换为实际的库名和表名），判断是否为无唯一键表。如果无返回结果，即可视为无唯一键表。

SELECT 1 FROM information_schema.statistics WHERE TABLE_SCHEMA = {schema} AND TABLE_NAME = {table} GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME HAVING count(1) = count(IF(upper(nullable) != 'YES' and NON_UNIQUE = 0, 1, NULL));

• 添加列

  ALTER TABLE `{schema}`.`{table}` ADD COLUMN `OMS_PK_INCRMT` BIGINT;

• 添加唯一索引

  // 有分区字段
  CREATE UNIQUE KEY `UK_{table}_OBPK_INCRMT` (`{partition_col_0}`, `{partition_col_1}`, `OMS_PK_INCRMT`) LOCAL;
  // 没有分区字段
  CREATE UNIQUE KEY `UK_{table}_OBPK_INCRMT` (`OMS_PK_INCRMT`);

• 删除唯一索引

  DROP INDEX `UK_{table}_OBPK_INCRMT` ON `{schema}`.`{table}`;

• 删除列

  ALTER TABLE `{schema}`.`{table}` DROP COLUMN `OMS_PK_INCRMT`;

OceanBase 数据库 Oracle 租户之间的数据迁移

迁移 OceanBase 数据库 Oracle 租户的数据至 OceanBase 数据库 Oracle 租户时，数据传输会对无主键表添加隐藏列和唯一索引。

您可以执行下述命令（其中 {schema}、{table} 需要替换为实际的库名和表名），判断是否为无主键表。如果无返回结果，即可视为无主键表。

SELECT 1 FROM ( SELECT DC.OWNER, DC.TABLE_NAME, DC.CONSTRAINT_NAME FROM ALL_CONS_COLUMNS DCC JOIN ALL_CONSTRAINTS DC ON DCC.CONSTRAINT_NAME = DC.CONSTRAINT_NAME AND DCC.OWNER = DC.OWNER JOIN ALL_TAB_COLUMNS DTC ON DCC.COLUMN_NAME = DTC.COLUMN_NAME AND DCC.OWNER = DTC.OWNER AND DCC.TABLE_NAME = DTC.TABLE_NAME WHERE DCC.OWNER = {schema} AND DCC.TABLE_NAME = {table} AND DC.CONSTRAINT_TYPE IN ('U', 'P') GROUP BY DC.OWNER, DC.TABLE_NAME, DC.CONSTRAINT_NAME HAVING COUNT(*) = COUNT( CASE DTC.NULLABLE WHEN 'Y' THEN NULL ELSE 1 END ) MINUS SELECT TABLE_OWNER, TABLE_NAME, INDEX_NAME FROM ALL_IND_EXPRESSIONS WHERE TABLE_OWNER = {schema} AND TABLE_NAME = {table} );

• 添加隐藏列

  ALTER TABLE "{schema}"."{table}" ADD "OMS_PK_INCRMT" NUMBER INVISIBLE;

• 添加唯一索引

  // 有分区字段
  CREATE UNIQUE INDEX "{schema}"."UK_{table}_OBPK_INCRMT" ON "{schema}"."{table}"("{partition_col_0}", "{partition_col_1}", "OMS_PK_INCRMT") LOCAL;
  // 没有分区字段
  CREATE UNIQUE INDEX "{schema}"."UK_{table}_OBPK_INCRMT" ON "{schema}"."{table}"("OMS_PK_INCRMT");

• 删除唯一索引

  DROP INDEX "{schema}"."UK_{table}_OBPK_INCRMT";

• 删除隐藏列

  ALTER TABLE "{schema}"."{table}" DROP COLUMN "OMS_PK_INCRMT";

OceanBase 数据库 Oracle 租户至 Oracle 数据库的数据迁移

迁移 OceanBase 数据库 Oracle 租户的数据至 Oracle 数据库时，OMS 会对无主键表添加隐藏列和唯一索引。

您可以执行下述命令（其中 {schema}、{table} 需要替换为实际的库名和表名），判断是否为无主键表。如果无返回结果，即可视为无主键表。

SELECT 1 FROM ( SELECT DC.OWNER, DC.TABLE_NAME, DC.CONSTRAINT_NAME FROM ALL_CONS_COLUMNS DCC JOIN ALL_CONSTRAINTS DC ON DCC.CONSTRAINT_NAME = DC.CONSTRAINT_NAME AND DCC.OWNER = DC.OWNER JOIN ALL_TAB_COLUMNS DTC ON DCC.COLUMN_NAME = DTC.COLUMN_NAME AND DCC.OWNER = DTC.OWNER AND DCC.TABLE_NAME = DTC.TABLE_NAME WHERE DCC.OWNER = {schema} AND DCC.TABLE_NAME = {table} AND DC.CONSTRAINT_TYPE IN ('U', 'P') GROUP BY DC.OWNER, DC.TABLE_NAME, DC.CONSTRAINT_NAME HAVING COUNT(*) = COUNT( CASE DTC.NULLABLE WHEN 'Y' THEN NULL ELSE 1 END ) MINUS SELECT TABLE_OWNER, TABLE_NAME, INDEX_NAME FROM ALL_IND_EXPRESSIONS WHERE TABLE_OWNER = {schema} AND TABLE_NAME = {table} );

• 添加隐藏列

  // Oracle 数据库 12C 以前版本
  ALTER TABLE "{schema}"."{table}" ADD "OMS_PK_INCRMT" NUMBER;
  // Oracle 数据库 12C 及以后版本
  ALTER TABLE "{schema}"."{table}" ADD "OMS_PK_INCRMT" NUMBER INVISIBLE;

• 添加唯一索引

  // 有分区字段
  CREATE UNIQUE INDEX "{schema}"."{table}" ON "{schema}"."{table}"("{partition_col_0}", "{partition_col_1}", "OMS_PK_INCRMT") LOCAL;
  // 没有分区字段
  CREATE UNIQUE INDEX "{schema}"."{table}" ON "{schema}"."{table}"("OMS_PK_INCRMT");

• 删除唯一索引

  DROP INDEX "{schema}"."{table}";

• 删除隐藏列

  ALTER TABLE "{schema}"."{table}" DROP COLUMN "OMS_PK_INCRMT";