ApsaraDB for OceanBase:Hidden column mechanism of the data transmission service

Background information

If you do not advance to the forward switchover step in a data migration project, you must manually drop the hidden columns and unique indexes based on the type of the data migration project as guided by this topic.

When you advance to the forward switchover step in a data migration project, the data transmission service will automatically drop the hidden columns and unique indexes based on the type of the data migration project.

• When you migrate data from an Oracle database to an Oracle tenant of OceanBase Database, the data transmission service will add hidden columns to all tables at the destination database, and create unique indexes based on the hidden columns. These columns and indexes will be automatically dropped during the forward switchover step. For more information about how to create a project, see Migrate data from an Oracle database to an Oracle tenant of OceanBase Database.

  The data transmission service will automatically add hidden columns to the CREATE TABLE statement. Here is an example:

  CREATE TABLE "table1" (
  	"S1" NVARCHAR2(2) NOT NULL,
  	"S2" NVARCHAR2(20) DEFAULT 'Default value field',
  	"OMS_OBJECT_NUMBER" NUMBER INVISIBLE,
    "OMS_RELATIVE_FNO" NUMBER INVISIBLE,
    "OMS_BLOCK_NUMBER" NUMBER INVISIBLE,
    "OMS_ROW_NUMBER" NUMBER INVISIBLE
  );

• For data migration projects listed in the following table, to ensure the migration of tables without a unique key, the data transmission service will add hidden columns for corresponding tables at the destination and create a unique index based on the hidden columns. These hidden columns and indexes will be automatically deleted at the forward switchover step.

  Source	Destination	Reference
  MySQL tenant of OceanBase Database	MySQL tenant of OceanBase Database	Migrate data within OceanBase Database
  Oracle tenant of OceanBase Database	Oracle tenant of OceanBase Database	Migrate data within OceanBase Database
  Oracle tenant of OceanBase Database	Oracle database	Migrate data from an Oracle tenant of OceanBase Database to an Oracle database

  • For data migration between MySQL tenants of OceanBase Database, the data transmission service will automatically add hidden columns, hidden column comments, and unique indexes to the CREATE TABLE statement.

    // Create a hidden column and a unique index for a table without a partitioning key column.
    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 a hidden column and a unique index for a table with a partitioning key column.
    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)
    );

  • For data migration between Oracle tenants of OceanBase Database, the data transmission service will automatically add hidden columns to the CREATE TABLE statement.

    // Versions earlier than Oracle Database 12c
    CREATE TABLE "table3" (
    	"id" NUMBER,
    	"status" VARCHAR2(1 BYTE),
    	"OMS_PK_INCRMT" NUMBER
    );
    // Oracle Database 12c and later
    CREATE TABLE "table3" (
    	"id" NUMBER,
    	"status" VARCHAR2(1 BYTE),
    	"OMS_PK_INCRMT" NUMBER INVISIBLE
    );

  • For data migration from an Oracle tenant of OceanBase Database to an Oracle database, the data transmission service will automatically add hidden columns to the CREATE TABLE statement.

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

Usage notes

• "{partition_col_0}" or `{partition_col_0}` indicates a table partitioning key column. Multiple such fields may exist. Separate the fields with commas (,), such as "{partition_col_0}", "{partition_col_1}", "{partition_col_2}" or `{partition_col_0}`, `{partition_col_1}`, `{partition_col_2}`.

• `{schema}`, `{table}`, `{partition_col_0}`, and `{partition_col_1}` are respectively placeholders for the database name, table name, and partitioning key column names.

Data migration from an Oracle database to an Oracle tenant of OceanBase Database

When you migrate data from an Oracle database to an Oracle tenant of OceanBase Database, the data transmission service will add hidden columns and create unique indexes for the tables, regardless of whether they have primary keys.

• Add hidden columns

  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;

• Add unique indexes

  // Partitioning key columns are available.
  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;
  // No partitioning key column is available.
  CREATE UNIQUE INDEX "{schema}"."UK_{table}_OMS_ROWID" ON "{schema}"."{table}" ("OMS_OBJECT_NUMBER", "OMS_RELATIVE_FNO", "OMS_BLOCK_NUMBER", "OMS_ROW_NUMBER");

• Drop unique indexes

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

• Drop hidden columns

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

Data migration between MySQL tenants of OceanBase Database

When you migrate data between MySQL tenants of OceanBase Database, the data transmission service will add columns and create unique indexes for tables without unique keys.

You can run the following command to check whether a table has unique keys. Replace {schema} and {table} with the actual database and table names respectively. If no result is returned, the table does not have unique keys.

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

• Add columns

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

• Add unique indexes

  // Partitioning key columns are available.
  CREATE UNIQUE KEY `UK_{table}_OBPK_INCRMT` (`{partition_col_0}`, `{partition_col_1}`, `OMS_PK_INCRMT`) LOCAL;
  // No partitioning key column is available.
  CREATE UNIQUE KEY `UK_{table}_OBPK_INCRMT` (`OMS_PK_INCRMT`);

• Drop unique indexes

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

• Remove columns

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

Data migration between Oracle tenants of OceanBase Database

When you migrate data between Oracle tenants of OceanBase Database, the data transmission service will add hidden columns and create unique indexes for tables without primary keys.

You can execute the following statement to check whether a table has a primary key. You must replace {schema} and {table} in the statement with the actual database name and table name. If no result is returned, the table has no primary key.

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

• Add hidden columns

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

• Add unique indexes

  // Partitioning key columns are available.
  CREATE UNIQUE INDEX "{schema}"."UK_{table}_OBPK_INCRMT" ON "{schema}"."{table}"("{partition_col_0}", "{partition_col_1}", "OMS_PK_INCRMT") LOCAL;
  // No partitioning key column is available.
  CREATE UNIQUE INDEX "{schema}"."UK_{table}_OBPK_INCRMT" ON "{schema}"."{table}"("OMS_PK_INCRMT");

• Drop unique indexes

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

• Drop hidden columns

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

Data migration from an Oracle tenant of OceanBase Database to an Oracle database

When you migrate data from an Oracle tenant of OceanBase Database to an Oracle database, OMS will add hidden columns and create unique indexes for tables without primary keys.

You can execute the following statement to check whether a table has a primary key. You must replace {schema} and {table} in the statement with the actual database name and table name. If no result is returned, the table has no primary key.

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

• Add hidden columns

  // Versions earlier than Oracle Database 12c
  ALTER TABLE "{schema}"."{table}" ADD "OMS_PK_INCRMT" NUMBER;
  // Oracle Database 12c and later
  ALTER TABLE "{schema}"."{table}" ADD "OMS_PK_INCRMT" NUMBER INVISIBLE;

• Add unique indexes

  // Partitioning key columns are available.
  CREATE UNIQUE INDEX "{schema}"."{table}" ON "{schema}"."{table}"("{partition_col_0}", "{partition_col_1}", "OMS_PK_INCRMT") LOCAL;
  // No partitioning key column is available.
  CREATE UNIQUE INDEX "{schema}"."{table}" ON "{schema}"."{table}"("OMS_PK_INCRMT");

• Drop unique indexes

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

• Drop hidden columns

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