ApsaraDB for OceanBase：資料移轉服務隱藏列機制說明

背景資訊

如果您在運行資料移轉專案時，沒有進入正向切換步驟，請根據本文不同資料移轉專案的操作，自行刪除隱藏列和唯一索引。

如果您在運行資料移轉專案時，進入了正向切換步驟，資料轉送會根據資料移轉專案的類型，自動刪除隱藏列和唯一索引：

• 遷移 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 語句中。

    //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
    );

  • OceanBase 資料庫 Oracle 租戶至 Oracle 資料庫的資料移轉，資料轉送會自動添加隱藏列至 CREATE TABLE 語句中。

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

前置說明

• "{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";